How to parse freeform street postal address out of text and into components

Question

We do business largely in the United States and are trying to improve user experience by combining all the address fields into a single text area  But there are a few problems    The address the user types may not be correct or in a standard format The address must be separated into parts  street  city  state  etc   to process credit card payments Users may enter more than just their address  like their name or company with it  Google can do this but the Terms of Service and query limits are prohibitive  especially on a tight budget   Apparently  this is a common question    PHP script to parse address  How do I parse the free format address to save into the DataBase java postal address parser More efficient way to extract address components How can i show a pre populated postal address in contacts screen with street  city  zip on android PHP regexp US address   Is there a way to isolate an address from the text around it and break it into pieces  Is there a regular expression to parse addresses

User · Answer

libpostal  an open-source library to parse addresses  training with data from OpenStreetMap  OpenAddresses and OpenCage   https   github com openvenues libpostal  more info about it   Other tools services    http   www gisgraphy com Free  open source  and ready to use geocoder and geolocalisation webservices  integrating OpenStreetMap  GeoNames and Quattroshapes  https   github com kodapan osm-common Library for accessing OpenStreetMap services  parsing and processing data  http   wiki openstreetmap org wiki Nominatim http   address-parser net  http   geoservices tamu edu Services AddressNormalization

User · Answer

No code  For shame   Here is a simple JavaScript address parser  It s pretty awful for every single reason that Matt gives in his dissertation above  which I almost 100  agree with  addresses are complex types  and humans make mistakes  better to outsource  and automate this - when you can afford to    But rather than cry  I decided to try   This code works OK for parsing most Esri results for findAddressCandidate and also with some other  reverse geocoders that return single-line address where street city state are delimited by commas  You can extend if you want or write country-specific parsers  Or just use this as case study of how challenging this exercise can be or at how lousy I am at JavaScript  I admit I only spent about thirty mins on this  future iterations could add caches  zip validation  and state lookups as well as user location context   but it worked for my use case  End user sees form that parses geocode search response into 4 textboxes  If address parsing comes out wrong  which is rare unless source data was poor  it s no big deal - the user gets to verify and fix it   But for automated solutions could either discard ignore or flag as error so dev can either support the new format or fix source data     x000D   x000D      x000D  address assumptions  x000D  - US addresses only  probably want separate parser for different countries  x000D  - No country code expected  x000D  - if last token is a number it is probably a postal code x000D  -- 5 digit number means more likely x000D  - if last token is a hyphenated string it might be a postal code x000D  -- if both sides are numeric  and in form      -     it is more likely x000D  - if city is supplied  state will also be supplied  city names not unique  x000D  - zip postal code may be omitted even if has city  amp  state x000D  - state may be two-char code or may be full state name  x000D  - commas   x000D  -- last comma is usually city state separator x000D  -- second-to-last comma is possibly street city separator x000D  -- other commas are building-specific stuff that I don t care about right now  x000D  - token count  x000D  -- because units  street names  and city names may contain spaces token count highly variable  x000D  -- simplest address has at least two tokens  714 OAK x000D  -- common simple address has at least four tokens  714 S OAK ST x000D  -- common full  mailing  address has at least 5-7  x000D  --- 714 OAK  RUMTOWN  VA 59201 x000D  --- 714 S OAK ST  RUMTOWN  VA 59201 x000D  -- complex address may have a dozen or more  x000D  --- MAGICICIAN SUPPLY  LLC  UNIT 213A  MAGIC TOWN MALL  13 MAGIC CIRCLE DRIVE  LAND OF MAGIC  MA 73122-3412 x000D     x000D   x000D  var rawtext      textarea   val    x000D  var rawlist   rawtext split   n    x000D   x000D  function ParseAddressEsri singleLineaddressString    x000D    var address     x000D      street      x000D      city      x000D      state      x000D      postalCode     x000D       x000D   x000D       tokenize by space  retain commas in tokens  x000D    var tokens   singleLineaddressString split    s      x000D    var tokenCount   tokens length  x000D    var lastToken   tokens pop    x000D    if   x000D         if numeric assume postal code  ignore length  for now  x000D       isNaN lastToken     x000D         if hyphenated assume long zip code  ignore whether numeric  for now x000D      lastToken split  -   length - 1     1    x000D      address postalCode   lastToken  x000D      lastToken   tokens pop    x000D      x000D   x000D    if  lastToken  amp  amp  isNaN lastToken     x000D      if  address postalCode length  amp  amp  lastToken length     2    x000D           assume state province code ONLY if had postal code x000D           otherwise it could be a simple address like  714 S OAK ST  x000D           where  ST  for  street  looks like two-letter state code x000D           possibly this could be resolved with registry of known state codes  but meh   and may collide anyway  x000D        address state   lastToken  x000D        lastToken   tokens pop    x000D        x000D      if  address state length     0    x000D           check for special case  might have State name instead of State Code  x000D        var stateNameParts    lastToken endsWith        lastToken substring 0  lastToken length - 1    lastToken   x000D   x000D           check remaining tokens from right-to-left for the first comma x000D        while  2   2    5    x000D          lastToken   tokens pop    x000D          if   lastToken  break  x000D          else if  lastToken endsWith         x000D               found separator  ignore stuff on left side x000D            tokens push lastToken      put it back x000D            break  x000D            else   x000D            stateNameParts unshift lastToken   x000D            x000D          x000D        address state   stateNameParts join       x000D        lastToken   tokens pop    x000D        x000D      x000D   x000D    if  lastToken    x000D         here is where it gets trickier  x000D      if  address state length    x000D           if there is a state  then assume there is also a city and street  x000D           PROBLEM  city may be multiple words  spaces  x000D           but we can pretty safely assume next-from-last token is at least PART of the city name x000D           most cities are single-name  It would be very helpful if we knew more context  like x000D           the name of the city user is in  But ignore that for now  x000D           ideally would have zip code service or lookup to give city name for the zip code  x000D        var cityNameParts    lastToken endsWith        lastToken substring 0  lastToken length - 1    lastToken   x000D   x000D           assumption   RULE  street and city must have comma delimiter x000D           addresses that do not follow this rule will be wrong only if city has space x000D           but don t care because Esri formats put comma before City x000D        var streetNameParts       x000D   x000D           check remaining tokens from right-to-left for the first comma x000D        while  2   2    5    x000D          lastToken   tokens pop    x000D          if   lastToken  break  x000D          else if  lastToken endsWith         x000D               found end of street address  may include building  etc  - don t care right now  x000D               add token back to end  but remove trailing comma  it did its job  x000D            tokens push lastToken endsWith        lastToken substring 0  lastToken length - 1    lastToken   x000D            streetNameParts   tokens  x000D            break  x000D            else   x000D            cityNameParts unshift lastToken   x000D            x000D          x000D        address city   cityNameParts join       x000D        address street   streetNameParts join       x000D        else   x000D           if there is NO state  then assume there is NO city also  just street   easy  x000D           reasoning  city names are not very original  Portland  OR and Portland  ME  so if user wants city they need to store state also  but if you are only ever in Portlan  OR  you don t care about city state  x000D           put last token back in list  then rejoin on space x000D        tokens push lastToken   x000D        address street   tokens join       x000D        x000D      x000D       when parsing right-to-left hard to know if street only vs street   city state x000D       hack fix for now is to shift stuff around  x000D       assumption requirement  will always have at least street part  you will never just get  city  state    x000D       could possibly tweak this with options or more intelligent parsing amp sniffing x000D    if   address city  amp  amp  address state    x000D      address city   address state  x000D      address state       x000D      x000D    if   address street    x000D      address street   address city  x000D      address city       x000D      x000D   x000D    return address  x000D    x000D   x000D     get list of objects with discrete address properties x000D  var addresses   rawlist x000D     filter function o    x000D      return o length  gt  0 x000D       x000D     map ParseAddressEsri   x000D      output   text JSON stringify addresses    x000D  console log addresses   x000D   lt script src  https   ajax googleapis com ajax libs jquery 2 1 1 jquery min js  gt  lt  script gt  x000D   lt textarea gt  x000D  27488 Stanford Ave  Bowden  North Dakota x000D  380 New York St  Redlands  CA 92373 x000D  13212 E SPRAGUE AVE  FAIR VALLEY  MD 99201 x000D  1005 N Gravenstein Highway  Sebastopol CA 95472 x000D  A  P  Croll  amp amp  Son 2299 Lewes-Georgetown Hwy  Georgetown  DE 19947 x000D  11522 Shawnee Road  Greenwood  DE 19950 x000D  144 Kings Highway  S W  Dover  DE 19901 x000D  Intergrated Const  Services 2 Penns Way Suite 405  New Castle  DE 19720 x000D  Humes Realty 33 Bridle Ridge Court  Lewes  DE 19958 x000D  Nichols Excavation 2742 Pulaski Hwy  Newark  DE 19711 x000D  2284 Bryn Zion Road  Smyrna  DE 19904 x000D  VEI Dover Crossroads  LLC 1500 Serpentine Road  Suite 100 Baltimore MD 21 x000D  580 North Dupont Highway  Dover  DE 19901 x000D  P O  Box 778  Dover  DE 19903 x000D  714 S OAK ST x000D  714 S OAK ST  RUM TOWN  VA  99201 x000D  3142 E SPRAGUE AVE  WHISKEY VALLEY  WA 99281 x000D  27488 Stanford Ave  Bowden  North Dakota x000D  380 New York St  Redlands  CA 92373 x000D   lt  textarea gt  x000D   lt div id  output  gt  x000D   lt  div gt  x000D   x000D   x000D

User · Answer

I saw this question a lot when I worked for an address verification company  I m posting the answer here to make it more accessible to programmers searching around with the same question  The company I was at processed billions of addresses  and we learned a lot in the process  First  we need to understand a few things about addresses  Addresses are not regular This means that regular expressions are out  I ve seen it all  from simple regular expressions that match addresses in a very specific format  to this     s   d 2 5  s      a p m b    a-zA-Z  s   1 5   1 2      s           a-zA-Z  s   1 30   1 4   court ct street st drive dr lane ln road rd blvd    s             a-zA-Z  s   1 30   1 2     s         b AK AL AR AZ CA CO CT DC DE FL GA GU HI IA ID IL IN KS KY LA MA MD ME MI MN MO MS MT NC ND NE NH NJ NM NV NY OH OK OR PA RI SC SD TN TX UT VA VI VT WA WI WV WY    s          s  d 5      s        i      to this where a 900  line-class file generates a supermassive regular expression on the fly to match even more  I don t recommend these  for example  here s a fiddle of the above regex  that makes plenty of mistakes   There isn t an easy magic formula to get this to work  In theory and by theory  it s impossible to match addresses with a regular expression  USPS Publication 28 documents the many formats of addresses that are possible  with all their keywords and variations  Worst of all  addresses are often ambiguous  Words can mean more than one thing   quot St quot  can be  quot Saint quot  or  quot Street quot    and there are words that I m pretty sure they invented   Who knew that  quot Stravenue quot  was a street suffix   You d need some code that really understands addresses  and if that code does exist  it s a trade secret  But you could probably roll your own if you re really into that  Addresses come in unexpected shapes and sizes Here are some contrived  but complete  addresses  1   102 main street     Anytown  state  2   400n 600e  2  52173  3   p o   104 60203  Even these are possibly valid  4   829 LKSDFJlkjsdflkjsdljf Bkpw 12345  5   205 1105 14 90210  Obviously  these are not standardized  Punctuation and line break are not guaranteed  Here s what s going on   Number 1 is complete because it contains a street address and a city and state  With that information  there s enough to identify the address  and it can be considered  quot deliverable quot   with some standardization    Number 2 is complete because it contains a street address  with secondary unit number  and a 5-digit ZIP code  which is enough to identify an address   Number 3 is a complete post office box format  as it contains a ZIP code   Number 4 is also complete because the ZIP code is unique  meaning that a private entity or corporation has purchased that address space  A unique ZIP code is for high-volume or concentrated delivery spaces  Anything addressed to ZIP code 12345 goes to General Electric in Schenectady  NY  This example won t reach anyone in particular  but the USPS would still deliver it   Number 5 is also complete  believe it or not  With just those numbers  the full address can be discovered when parsed against a database of all possible addresses  Filling in the missing directionals  secondary designator  and ZIP 4 code is trivial when you see each number as a component  Here s what it looks like  fully expanded and standardized     205 N 1105 W Apt 14 Beverly Hills CA 90210-5221  Address data is not your own In most countries that provide official address data to licensed vendors  the address data itself belongs to the governing agency  In the US  the USPS owns the addresses  The same is true for Canada Post  Royal Mail  and others  though each country enforces or defines ownership a little differently  Knowing this is important since it usually forbids reverse-engineering the address database  You have to be careful how to acquire  store  and use the data  Google Maps is a common go-to for quick address fixes  but the TOS is rather prohibitive  for example  you can t use their data or APIs without showing a Google Map  and for non-commercial purposes only  unless you pay   and you can t store the data  except for temporary caching   Makes sense  Google s data is some of the best in the world  However  Google Maps does not verify the address  If an address does not exist  it will still show you where the address would be if it did exist  try it on your own street  use a house number that you know doesn t exist   This is useful sometimes  but be aware of that  Nominatim s usage policy is similarly limiting  especially for high volume and commercial use  and the data is mostly drawn from free sources  so it isn t as well maintained  such as the nature of open projects   However  this may still suit your needs  A great community supports it  The USPS itself has an API  but it goes down a lot and comes with no guarantees nor support  It might also be hard to use  Some people use it sparingly with no problems  But it s easy to miss that the USPS requires that you use their API only for confirming addresses to ship through them  People expect addresses to be hard Unfortunately  we ve conditioned our society to expect addresses to be complicated  There are dozens of good UX articles all over the Internet about this  Still  the fact is  if you have an address form with individual fields  that s what users expect  even though it makes it harder for edge-case addresses that don t fit the format the form is expecting  or maybe the form requires a field it shouldn t  Or users don t know where to put a certain part of their address  I could go on and on about the bad UX of checkout forms these days  but instead  I ll say that combining the addresses into a single field will be a welcome change -- people will be able to type their address how they see fit  rather than trying to figure out your lengthy form  However  this change will be unexpected and users may find it a little jarring at first  Just be aware of that  Part of this pain can be alleviated by putting the country field out front  before the address  When they fill out the country field first  you know how to make your form appear  Maybe you have a good way to deal with single-field US addresses  so if they select the United States  you can reduce your form to a single field  otherwise show the component fields  Just things to think about  Now we know why it s hard  what can you do about it  The USPS licenses vendors through a process called CASS    Certification to provide verified addresses to customers  These vendors have access to the USPS database  updated monthly  Their software must conform to rigorous standards to be certified  and they don t often require agreement to such limiting terms as discussed above  Many CASS-Certified companies can process lists or have APIs  Melissa Data  Experian QAS  and SmartyStreets  to name a few   Due to getting flak for  quot advertising  quot  I ve truncated my answer at this point  It s up to you to find a solution that works for you   The Truth  Really  folks  I don t work at any of these companies  It s not an advertisement

User · Answer

UPDATE  Geocode xyz now works worldwide  For examples see https   geocode xyz  For USA  Mexico and Canada  see geocoder ca   For example      Input  something going on near the intersection of main and arthur kill rd new york      Output        lt geodata gt     lt latt gt 40 5123510000 lt  latt gt     lt longt gt -74 2500500000 lt  longt gt     lt AreaCode gt 347 718 lt  AreaCode gt     lt TimeZone gt America New York lt  TimeZone gt     lt standard gt       lt street1 gt main lt  street1 gt       lt street2 gt arthur kill lt  street2 gt       lt stnumber  gt       lt staddress  gt       lt city gt STATEN ISLAND lt  city gt       lt prov gt NY lt  prov gt       lt postal gt 11385 lt  postal gt       lt confidence gt 0 9 lt  confidence gt     lt  standard gt   lt  geodata gt     You may also check the results in the web interface or get output as Json or Jsonp  eg  I m looking for restaurants around 123 Main Street  New York

User · Answer

For US Address Parsing   I prefer using usaddress package that is available in pip for usaddress only  python3 -m pip install usaddress   Documentation PyPi  This worked well for me for US address      usr bin env python3   - - coding  utf-8 - -    address parser py import sys from usaddress import tag from json import dumps  loads  if   name         main         tag mapping              Recipient    recipient            AddressNumber    addressStreet            AddressNumberPrefix    addressStreet            AddressNumberSuffix    addressStreet            StreetName    addressStreet            StreetNamePreDirectional    addressStreet            StreetNamePreModifier    addressStreet            StreetNamePreType    addressStreet            StreetNamePostDirectional    addressStreet            StreetNamePostModifier    addressStreet            StreetNamePostType    addressStreet            CornerOf    addressStreet            IntersectionSeparator    addressStreet            LandmarkName    addressStreet            USPSBoxGroupID    addressStreet            USPSBoxGroupType    addressStreet            USPSBoxID    addressStreet            USPSBoxType    addressStreet            BuildingName    addressStreet            OccupancyType    addressStreet            OccupancyIdentifier    addressStreet            SubaddressIdentifier    addressStreet            SubaddressType    addressStreet            PlaceName    addressCity            StateName    addressState            ZipCode    addressPostalCode             try          address      tag     join sys argv 1     tag mapping tag mapping      except          with open  failed address txt    a   as fp              fp write sys argv 1      n           print dumps          else          print dumps dict address      Running the address parser py   python3 address parser py 9757 East Arcadia Ave  Saugus MA 01906    addressStreet    9757 East Arcadia Ave     addressCity    Saugus    addressState    MA    addressPostalCode    01906

User · Answer

I m late to the party  here is an Excel VBA script I wrote years ago for Australia  It can be easily modified to support other Countries  I ve made a GitHub repository of the C  code here   I ve hosted it on my site and you can download it here  http   jeremythompson net rocks ParseAddress xlsm  Strategy  For any country with a PostCode that s numeric or can be matched with a RegEx my strategy works very well    First we detect the First and Surname which are assumed to be the top line  Its easy to skip the name and start with the address by unticking the checkbox  called  Name is top row  as shown below   Next its safe to expect the Address consisting of the Street and Number come before the Suburb and the St  Pde  Ave  Av  Rd  Cres  loop  etc is a separator   Detecting the Suburb vs the State and even Country can trick the most sophisticated parsers as there can be conflicts  To overcome this I use a PostCode look up based on the fact that after stripping Street and Apartment Unit numbers as well as the PoBox Ph Fax Mobile etc  only the PostCode number will remain  This is easy to match with a regEx to then look up the suburb s  and country       Your National Post Office Service will provide a list of post codes with Suburbs and States free of charge that you can store in an excel sheet  db table  text json xml file  etc     Finally  since some Post Codes have multiple Suburbs we check which suburb appears in the Address      Example    VBA Code  DISCLAIMER  I know this code is not perfect  or even written well however its very easy to convert to any programming language and run in any type of application The strategy is the answer depending on your country and rules  take this code as an example   Option Explicit  Private Const TopRow As Integer   0  Public Sub ParseAddress   Dim strArr   As String Dim sigRow   As String Dim i As Integer Dim j As Integer Dim k As Integer Dim Stat As String Dim SpaceInName As Integer Dim Temp As String Dim PhExt As String  On Error Resume Next  Temp   ActiveSheet Range  Address     Split info into array strArr   Split Temp  vbLf    Trim the array For i   0 To UBound strArr  strArr i    VBA Trim strArr i   Next i   Remove empty items rows     ReDim sigRow LBound strArr  To UBound strArr   For i   LBound strArr  To UBound strArr      If Trim strArr i    lt  gt     Then         sigRow j    strArr i          j   j   1     End If Next i ReDim Preserve sigRow LBound strArr  To j    Find the name  MUST BE ON THE FIRST ROW UNLESS CHECKBOX UNTICKED  i   TopRow If ActiveSheet Shapes  chkFirst   ControlFormat Value   1 Then  SpaceInName   InStr 1  sigRow i        vbTextCompare  - 1  If ActiveSheet Shapes  chkConfirm   ControlFormat Value   0 Then ActiveSheet Range  FirstName     VBA Left sigRow i   SpaceInName  Else  If MsgBox  First Name     amp  VBA Mid  sigRow i   1  SpaceInName   vbQuestion   vbYesNo   Confirm Details     vbYes Then ActiveSheet Range  FirstName     VBA Left sigRow i   SpaceInName  End If  If ActiveSheet Shapes  chkConfirm   ControlFormat Value   0 Then ActiveSheet Range  Surname     VBA Mid sigRow i   SpaceInName   2  Else   If MsgBox  Surame     amp  VBA Mid sigRow i   SpaceInName   2   vbQuestion   vbYesNo   Confirm Details     vbYes Then ActiveSheet Range  Surname     VBA Mid sigRow i   SpaceInName   2  End If sigRow i       End If   Find the Street by looking for a  St  Pde  Ave  Av  Rd  Cres  loop  etc  For i   1 To UBound sigRow  If Len sigRow i    gt  0 Then     For j   0 To 8     If InStr 1  VBA UCase sigRow i    Street j   vbTextCompare   gt  0 Then       Find the position of the street in order to get the suburb     SpaceInName   InStr 1  VBA UCase sigRow i    Street j   vbTextCompare    Len Street j   - 1       If its a po box then add 5 chars     If VBA Right Street j   3     BOX  Then SpaceInName   SpaceInName   5      If ActiveSheet Shapes  chkConfirm   ControlFormat Value   0 Then     ActiveSheet Range  Street     VBA Mid sigRow i   1  SpaceInName      Else       If MsgBox  Street Address     amp  VBA Mid sigRow i   1  SpaceInName   vbQuestion   vbYesNo   Confirm Details     vbYes Then ActiveSheet Range  Street     VBA Mid sigRow i   1  SpaceInName      End If      Trim the Street  Number leaving the Suburb if its exists on the same line     sigRow i    VBA Mid sigRow i   SpaceInName    2     sigRow i    Replace sigRow i   VBA Mid sigRow i   1  SpaceInName            GoTo PastAddress      End If     Next j End If Next i PastAddress    Mobile For i   1 To UBound sigRow  If Len sigRow i    gt  0 Then     For j   0 To 3     Temp   Mb j          If VBA Left VBA UCase sigRow i    Len Temp     Temp Then         If ActiveSheet Shapes  chkConfirm   ControlFormat Value   0 Then         ActiveSheet Range  Mobile     VBA Mid sigRow i   Len Temp    2          Else           If MsgBox  Mobile     amp  VBA Mid sigRow i   Len Temp    2   vbQuestion   vbYesNo   Confirm Details     vbYes Then ActiveSheet Range  Mobile     VBA Mid sigRow i   Len Temp    2          End If     sigRow i           GoTo PastMobile      End If     Next j End If Next i PastMobile    Phone For i   1 To UBound sigRow  If Len sigRow i    gt  0 Then     For j   0 To 1     Temp   Ph j          If VBA Left VBA UCase sigRow i    Len Temp     Temp Then               TODO  Detect the intl or national extension here   or if we can from the postcode              If ActiveSheet Shapes  chkConfirm   ControlFormat Value   0 Then             ActiveSheet Range  Phone     VBA Mid sigRow i   Len Temp    3              Else               If MsgBox  Phone     amp  VBA Mid sigRow i   Len Temp    3   vbQuestion   vbYesNo   Confirm Details     vbYes Then ActiveSheet Range  Phone     VBA Mid sigRow i   Len Temp    3              End If          sigRow i               GoTo PastPhone          End If     Next j End If Next i PastPhone     Email For i   1 To UBound sigRow      If Len sigRow i    gt  0 Then          replace with regEx search         If InStr 1  sigRow i        vbTextCompare  And InStr 1  VBA UCase sigRow i      CO   vbTextCompare  Then         Dim email As String         email   sigRow i          email   Replace VBA UCase email    EMAIL                email   Replace VBA UCase email    E-MAIL                email   Replace VBA UCase email    E                email   Replace VBA UCase Trim email     E                email   VBA LCase email               If ActiveSheet Shapes  chkConfirm   ControlFormat Value   0 Then             ActiveSheet Range  Email     email             Else               If MsgBox  Email     amp  email  vbQuestion   vbYesNo   Confirm Details     vbYes Then ActiveSheet Range  Email     email             End If         sigRow i               Exit For         End If     End If Next i   Now the only remaining items will be the postcode  suburb  country  there shouldn t be any numbers  eg  from PoBox Ph Fax Mobile  except for the Post Code   Join the string and filter out the Post Code Temp   Join sigRow  vbCrLf  Temp   Trim Temp   For i   1 To Len Temp   Dim postCode As String postCode   VBA Mid Temp  i  4    In Australia PostCodes are 4 digits If VBA Mid Temp  i  1   lt  gt      And IsNumeric postCode  Then      If ActiveSheet Shapes  chkConfirm   ControlFormat Value   0 Then     ActiveSheet Range  PostCode     postCode     Else       If MsgBox  Post Code     amp  postCode  vbQuestion   vbYesNo   Confirm Details     vbYes Then ActiveSheet Range  PostCode     postCode     End If       Lookup the Suburb and State based on the PostCode  the PostCode sheet has the lookup     Dim mySuburbArray As Range     Set mySuburbArray   Sheets  PostCodes   Range  A2 B16670        Dim suburbs As String     For j   1 To mySuburbArray Columns 1  Cells Count     If mySuburbArray Cells j  1    postCode Then          Check if the suburb is listed in the address         If InStr 1  UCase Temp   mySuburbArray Cells j  2   vbTextCompare   gt  0 Then           Set the Suburb and State         ActiveSheet Range  Suburb     mySuburbArray Cells j  2          Stat   mySuburbArray Cells j  3          ActiveSheet Range  State     Stat           Knowing the State - for Australia we can get the telephone Ext         PhExt   PhExtension VBA UCase Stat           ActiveSheet Range  PhExt     PhExt           remove the phone extension from the number         Dim prePhone As String         prePhone   ActiveSheet Range  Phone           prePhone   Replace prePhone  PhExt  amp                   prePhone   Replace prePhone       amp  PhExt  amp                    prePhone   Replace prePhone       amp  PhExt  amp                   ActiveSheet Range  Phone     prePhone         Exit For         End If     End If     Next j Exit For End If Next i  End Sub   Private Function PhExtension ByVal State As String  As String Select Case State Case Is    NSW  PhExtension    02  Case Is    QLD  PhExtension    07  Case Is    VIC  PhExtension    03  Case Is    NT  PhExtension    04  Case Is    WA  PhExtension    05  Case Is    SA  PhExtension    07  Case Is    TAS  PhExtension    06  End Select End Function  Private Function Ph ByVal Num As Integer  As String Select Case Num Case Is   0 Ph    PH  Case Is   1 Ph    PHONE   Case Is   2  Ph    P  End Select End Function  Private Function Mb ByVal Num As Integer  As String Select Case Num Case Is   0 Mb    MB  Case Is   1 Mb    MOB  Case Is   2 Mb    CELL  Case Is   3 Mb    MOBILE   Case Is   4  Mb    M  End Select End Function  Private Function Fax ByVal Num As Integer  As String Select Case Num Case Is   0 Fax    FAX  Case Is   1 Fax    FACSIMILE   Case Is   2  Fax    F  End Select End Function  Private Function State ByVal Num As Integer  As String Select Case Num Case Is   0 State    NSW  Case Is   1 State    QLD  Case Is   2 State    VIC  Case Is   3 State    NT  Case Is   4 State    WA  Case Is   5 State    SA  Case Is   6 State    TAS  End Select End Function  Private Function Street ByVal Num As Integer  As String Select Case Num Case Is   0 Street     ST  Case Is   1 Street     RD  Case Is   2 Street     AVE  Case Is   3 Street     AV  Case Is   4 Street     CRES  Case Is   5 Street     LOOP  Case Is   6 Street    PO BOX  Case Is   7 Street     STREET  Case Is   8 Street     ROAD  Case Is   9 Street     AVENUE  Case Is   10 Street     CRESENT  Case Is   11 Street     PARADE  Case Is   12 Street     PDE  Case Is   13 Street     LANE  Case Is   14 Street     COURT  Case Is   15 Street     BLVD  Case Is   16 Street    P O  BOX  Case Is   17 Street    P O BOX  Case Is   18 Street    PO BOX  Case Is   19 Street    POBOX  End Select End Function

User · Answer

There are many street address parsers  They come in two basic flavors - ones that have databases of place names and street names  and ones that don t    A regular expression street address parser can get up to about a 95  success rate without much trouble  Then you start hitting the unusual cases   The Perl one in CPAN   Geo  StreetAddress  US   is about that good  There are Python and Javascript ports of that  all open source   I have an improved version in Python which moves the success rate up slightly by handling more cases   To get the last 3  right  though  you need databases to help with disambiguation    A database with 3-digit ZIP codes and US state names and abbreviations is a big help   When a parser sees a consistent postal code and state name  it can start to lock on to the format   This works very well for the US and UK     Proper street address parsing starts from the end and works backwards  That s how the USPS systems do it   Addresses are least ambiguous at the end  where country names  city names  and postal codes are relatively easy to recognize   Street names can usually be isolated   Locations on streets are the most complex to parse  there you encounter things such as  Fifth Floor  and  Staples Pavillion    That s when a database is a big help

[api] How to parse freeform street/postal address out of text, and into components

Examples related to api

Examples related to parsing

Examples related to street-address