How to use Excel formulas, or quick tricks, to split a full address into columns for street, city, state and zip code. Formula examples use text functions -- LEFT, RIGHT, MID, SUBSTITUTE, FIND, SEARCH and more.
In the examples below, the full address is in a worksheet cell. The goal is to split the Street Address, City, State and Zip Code into separate columns, in the same row.
First, there are two quick methods shown below, to separate the data into different columns, without using formulas.
With these two non-formula methods, the data is separated into columns, and is no longer connected to the original full address.
These solutions are quick and easy, if you don't plan to change the "full address" cell in the future.
For example,
If there is a consistent pattern to the way the full address is entered in each cell, the built-in Flash Fill feature is a quick way to split the address into separate columns.
In this screen shot, there is a consistent pattern to the address data in each cell:
Follow these steps to split the address with a Flash Fill:
The Excel Flash Fill completes the entries in all of the columns, based on the manual entries in the first row.
In this short video, Sarah shows how to separate data from one cell, into multiple columns, using the Flash Fill Shortcut - Ctrl+E
In this example, the full address is in column A, with the Street Address, City, State and Zip Code all in one cell, separated by commas.
To split the Street Address, City, State and Zip Code into separate columns, without formulas, you can use Excel built-in Text to Columns feature.
Follow these steps to split the address with the Text to Columns feature:
The Convert Text to Columns Wizard opens, showing Step 1 of 3
In Step 2 of 3, you'll set the delimiters for your data:
In Step 3 of 3, you'll set the final details for your data:
The Excel Text to Columns feature splits each address into columns, and the full address remains in column A.
In this example, the full address is in column A, with the Street Address, City, State and Zip Code all in one cell, separated by commas.
To split the Street Address, City, State and Zip Code into separate columns, you can use Excel formulas - see the details below.
With these formulas, the data is separated into columns, and the parts of the address, created from formulas, are still connected to the original full address.
It takes a bit longer to set up formulas, instead of using Flash Fill or Text to Columns. However, it's worth the effort, if you plan to change the "full address" cells in the future.
For example,
In each full address, there are 3 comma separators:
To find the position of each comma, 3 new columns will be added to the table.
First, a named range is created on the worksheet in cell F1.
This named cell makes it easy to do a couple of things:
Three new columns were added to the address table, with these headings: Cm1, Cm2, Cm3
Here are the formulas in those columns, to find the positions of the 1st, 2nd, and 3rd commas in the full address.
All 3 formulas use the FIND function, with these 2 arguments:
The FIND function has an optional 3rd argument -- start_num
To return the street address from the full address, the formula uses the LEFT functions:
=LEFT(A4,F4-1)
The number of characters in the street address is calculated from the position of the first comma (in cell F4), minus 1.
To return the city name from the full address, the formula uses the MID function
=MID(A4,F4+1,G4-F4-1)
The MID function has 3 arguments:
To return the state code from the full address, the formula uses the MID function. It's similar to the City formula, but uses the positions of the 2nd and 3rd commas
=MID(A4,G4+1,H4-G4-1)
The MID function has 3 arguments:
Finally, to return the zip code, the formula uses the RIGHT and LEN functions:
=RIGHT(A4,LEN(A4)-H4)
The number of characters in the street address is calculated from:
In my weekly Excel newsletter, I challenged readers to use formulas to split a full address into four columns - Street Address, City, State and Zip Code.
The sample data is shown below, and you can download the Excel workbook, to see the challenge and the solutions.
One formula for each field is shown below, and the rest are in the sample workbook.
The addresses are in a named Excel table, with the full address in column B. Data isn't always this consistent, but in this example there are some characters that will help split the data:
In the FullAddress cell, the street address ends with " - ". Here is one formula that returns the street address.
The following formula uses the LEFT function to return text from the left side of a cell, and FIND to calculate the number of characters:
In that formula, the FIND function get the exact position of the hyphen
Then, subtract 2, to adjust for the space and hyphen
Finally, use the LEFT function, to return that number of characters at the left side of the FullAddress.
=LEFT([@FullAddress], FIND("-",[@FullAddress])-2)
In the FullAddress cell, the city is after the " - ", and ends with a comma. Here is one formula that returns the city. It uses the MID function which returns text based on a starting position, and number of characters:
To get the starting position, the LEN function get the length of the StreetAddress, which was previously calculated. Then, add 4 to that number, to adjust for the " - " string.
Two functions are used to calculate the number of characters -- SUBSTITUTE and FIND
The SUBSTITUTE function returns the FullAddress with the StreetAddress replaced by an empty string
The FIND function returns the position of the comma in that shortened address, and 4 is subtracted to adjust for the " -" string.
In the FullAddress cell, the State is after the city, and is 2 characters. Here is one formula that returns the State. It uses the MID function which returns text based on a starting position, and number of characters:
To get the starting position, the FIND function locates the City name (previously calculated) in the FullAddress cell.
The State starts after the city name, so the LEN function calculates the length of the city name. Then, 2 is added to that number, to adjust for the comma and space.
The state code is always 2 characters, so that number is typed into the formula. It does not need to be calculated.
=MID([@FullAddress], FIND([@City],[@FullAddress]) +LEN([@City])+2, 2)
In the FullAddress cell, the zip code is at the right side, after the state. The zip code is always 5 or 9 digits long. Here is one formula that returns the zip code. It uses the MID function which returns text based on a starting position, and number of characters:
To get the starting position, the FIND function locates the State code (previously calculated) in the FullAddress cell.
Then, 3 is added to that number, to adjust for the 2-letter State code, and the space.
The zip code is always 5 or 9 characters, so the higer number (9) is typed into the formula. It does not need to be calculated. That will return up to a maximum of 9 characters, and works for both types of zip codes.
=MID([@FullAddress], FIND([@State],[@FullAddress])+3, 9)
In this challenge, a few people said that they would use Flash Fill, instead of formulas, to split the addresses. Flash Fill was introduced in Excel 2013, 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.
Flash Fill isn't right for every job, but is helpful in some cases:
Here's how to use Flash Fill in the Split Address challenge:
Note: The screen shot is an animated gif, so it might not work in all browsers.
Split Address Examples: To see the split address examples for Flash Fill, Text to Columns, and formulas based on comma positions, download the Split Address Examples workbook. The zipped file is in xlsx format, and does not contain any macros.
Split Address Challenge: To see the Split Address challenge, and suggested solutions, download the Split Address Challenge workbook. The zipped file is in xlsx format, and does not contain any macros.
Last updated: January 22, 2022 1:35 PM