Concatenate and Parse Address Data Using Excel Formulas

Address data comes in all different forms, but sometimes it’s important to standardize addresses for use in an address label, or as input to software, such as our address and zip code add-in for Excel, CDXZipStream.   In this article we’ll be showing you some easy methods to

- combine (concatenate) address components into a single line.  If the individual components are given as “123 Main Street” “Springfield” “IL” “62701”, these would be concatenated into the single-line address  “123 Main Street, Springfield, IL 62701”  

- separate (parse) a single line address into its individual components .  For instance, the single line address (“123 Main Street, Springfield, IL 62701”  would be parsed into its street, city, state and zip code components as follows: “123 Main Street” “Springfield” “IL” “62701”

Both of these can be performed by using formulas in Microsoft Excel.

To combine address components, we can use the concatenation symbol “&” or the “CONCATENATE” function;  they both perform the same operation.  The formulas will also need to concatenate any commas and/or spaces that have to be added to the address.  For the example above, the formulas would be:

="123 Main Street"&", "&"Springfield"&", "&"IL"&" "&"62701"

=CONCATENATE("123 Main Street",", ","Springfield",", ","IL"," ","62701")

These same formulas can be more generally applied to combine the contents of worksheet cells, by using the cell address of each component:

=A1&", "&B1&", "&C1&" "&D1

=CONCATENATE(A1,”,”,B1,", ",C1," ",D1)

The big advantage of these more generalized formulas is that they can be copied to long lists of addresses and the cell reference will automatically adjust to concatenate the contents of the entire list.

Parsing a single line address is a little more complex, and needs to take into account the format of the address.  In this case we use a combination of the Excel text functions FIND, LEFT, RIGHT, and MID.  The FIND function finds the location in the address string where either a comma and/or space is used, and then this information is applied to extract the address component with either LEFT, RIGHT, or MID.  Assuming that the address takes the common form where the street, city, and state are separated by commas, and only a single space precedes the zip code, here is how to parse the address “123 Main Street, Springfield, IL 62701”, which for this example is located in cell A1:

=LEFT(A1,FIND(",",A1,1) -1)

Returns “123 Main Street”

=MID(A1,FIND(",",A1,1)+2,FIND(",",A1,FIND(",",A1,1)+1)-FIND(",",A1,1)-2)

Returns “Springfield”

=MID(A1,FIND(",",A1,FIND(",",A1,1)+1)+2,FIND(" ",A1,FIND(",",A1,FIND(",",A1,1)+1)+2)-FIND(",",A1,FIND(",",A1,1)+1)-2)

Returns “IL”

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(",",A1,FIND(",",A1,1)+1)+2))

Returns “62701”

These formulas will also work if the full state name is used (for this example, “Illinois” would be returned), and will return ZIP+4 codes as well.  If you only want to return the five-digit portion of a ZIP+4, use the following formula:

=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(",",A1,FIND(",",A1,1)+1)+2)),5)

You can copy and paste these formulas to your own Excel worksheet if you need to parse addresses in a list.  From the Excel toolbar perform a find for the cell reference “A1” and replace it with the first cell in your address list; then copy the modified formulas to apply it to the entire list.

CDXZipStream can also parse addresses and at the same time verify if the address data is valid, using Microsoft MapPoint running in the background.  This avoids messy formulas and does not require strict address formats.  Please see our blog article “How to Parse and Verify Address Data Using CDXZipStream”.

Comments are closed