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.
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.
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.
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
Example 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 sample 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.
_______________