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:

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 · Accepted 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 ("St" can be "Saint" or "Street"), and there are words that I'm pretty sure they invented. (Who knew that "Stravenue" 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 "deliverable" (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 "advertising," 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

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

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

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

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

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

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

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

The answer is

Addresses are not regular

Addresses come in unexpected shapes and sizes

Address data is not your own

People expect addresses to be hard

Now we know why it's hard; what can you do about it?

Strategy

Example

VBA Code

Examples related to api

Examples related to parsing

Examples related to street-address

Tags