Split Address With Excel Formulas

If a cell contains a full address, what formulas would you use to show the street address, city, state and zip code in separate cells? That was the challenge that I gave to my weekly newsletter readers. A few rows of the sample data are show below, and you can download the Split Address With Excel Formulas sample file, to see all the data.

Split Address With Formulas in Excel http://blog.contextures.com/

The Sample Data

For this challenge, I kept the data pretty clean – you might not be so lucky in real life!. Here are the first few rows of full addresses, with some key elements highlighted.

Split Address With Excel Formulas

Where to Split the Address

As you can see in the screen shot above, there are consistent characters that will help you split the data:

  • There is a hyphen after the street address, with a space character before and after it
  • There is a comma at the end of the city name, then a space character (Some of the street addresses also contain a comma)
  • The state is a 2-letter code, in upper case, with a space character before and after it
  • The zip code is 9 or 5 numbers, at the end of the address (Note: a zip code could start with a zero)

Flash Fill to Split Address

Some people said they would just use Flash Fill to split the addresses. That’s a quick and easy option if the full addresses won’t be edited later.

Flash Fill is available in Excel 2013 and later, and it helps you extract information, based on patterns in your data. You can use it to put names in reverse order, split dates and times, and many other data tasks.

This animated gif shows how to fill in the first two rows, then use the Flash Fill command in each column, to get the data.

Flash Fill to Split Addresses

Formulas to Split Address

If the full addresses might be edited later, Flash Fill entries won’t be updated automatically. Instead, you can use formulas to extract the separate parts from the full address.

I was amazed to see how many different solutions that people sent in. No two people used all the same formulas, and there were hardly any duplicates within each column.

However, there were a few functions that were key to solving the challenge:

  • SEARCH or FIND, to locate a specific character in the full address
  • LEFT, MID and RIGHT to return characters from a starting position

Split Address With Excel Formulas

Here are example formulas — one for each of the fields. You can download the sample workbook, to see all the other solutions:

  • Street Address: =LEFT([@FullAddress], FIND(“-“,[@FullAddress])-2)
  • City: =MID([@FullAddress],LEN([@StreetAddress])+4, FIND(“,”,SUBSTITUTE([@FullAddress],[@StreetAddress],””))-4)
  • State: =MID([@FullAddress],FIND([@City],[@FullAddress])+LEN([@City])+2,2)
  • Zip Code: =MID([@FullAddress],(FIND([@State],[@FullAddress]))+3,9)

For details on how these formulas work, go to the Split Address Formulas page on my Contextures website.

Get the Sample Workbook

To see the full address data, and all the solutions, download the Split Address With Excel Formulas workbook from the Split Address Formulas page on my Contextures website (scroll to the Download section).

The zipped file is in xlsx format, and does not contain any macros.

 

Split Address With Formulas in Excel http://blog.contextures.com/

 

_______________

Save

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *