Split Address With Formulas

How to use Excel formulas to split a full address into columns for street, city, state and zip code. Examples use text functions -- LEFT, RIGHT, MID, SUBSTITUTE, FIND, SEARCH and more.

Excel Split Address Problem

In my weekly Excel newsletter, I asked 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 problem and the solutions.

One formula for each field is shown below, and the rest are in the sample workbook.

Full Address

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:

• After the street address, there is a hyphen, with a space character before and after it
• At the end of the city name, there is a comma and 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 (Note: a zip code could start with a zero)

Street Address Formula

In the FullAddress cell, the street address ends with " - ". Here is one formula that returns the street address. It uses the LEFT function to return text from the left side of a cell, and FIND to calculate the number of characters:

• =LEFT([@FullAddress], FIND("-",[@FullAddress])-2 )

In that formula, the FIND function get the exact position of the hyphen

• FIND("-",[@FullAddress])

Then, subtract 2, to adjust for the space and hyphen

• FIND("-",[@FullAddress])-2

Finally, use the LEFT function, to return that number of characters at the left side of the FullAddress.

=LEFT([@FullAddress], FIND("-",[@FullAddress])-2)

City Formula

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:

• =MID([@FullAddress],LEN([@StreetAddress])+4, FIND(",",SUBSTITUTE([@FullAddress],[@StreetAddress],""))-4)

Starting Position

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.

• LEN([@StreetAddress])+4

Number of Characters

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

• SUBSTITUTE([@FullAddress],[@StreetAddress],"")

The FIND function returns the position of the comma in that shortened address, and 4 is subtracted to adjust for the " -" string.

• FIND(",",SUBSTITUTE([@FullAddress],[@StreetAddress],""))-4

State Formula

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:

• =MID([@FullAddress], FIND([@City],[@FullAddress]) +LEN([@City])+2,2)

Starting Position

To get the starting position, the FIND function locates the City name (previously calculated) in the FullAddress cell.

• FIND([@City],[@FullAddress])

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.

• +LEN([@City])+2

Number of Characters

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)

Zip Code Formula

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:

• =MID([@FullAddress], FIND([@State],[@FullAddress])+3, 9)

Starting Position

To get the starting position, the FIND function locates the State code (previously calculated) in the FullAddress cell.

• FIND([@State],[@FullAddress])

Then, 3 is added to that number, to adjust for the 2-letter State code, and the space.

• FIND([@State],[@FullAddress]) + 3

Number of Characters

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)

Flash Fill or Formulas

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:

• Flash Fill Pros: Quick and easy way to extract data. No formulas to slow down a big workbook.
• Flash Fill Cons: Items don't automatically change if the original data is updated. Data needs a very consistent pattern (no IF/OR options that a formula could provide).

Here's how to use Flash Fill in the Split Address challenge:

• Enter the address information in separate columns, in the first two rows (Type an apostrophe at the start of the zip code, in case any start with a zero)
• Then, click in cell C6, go the Data tab of the Excel Ribbon, and click Flash Fill.
• Repeat for cells D6, E6 and F6.

Note: The screen shot is an animated gif, so it might not work in all browsers.

Download the Sample File

To see the Split Address challenge, and suggested solutions, download the sample workbook. The zipped file is in xlsx format, and does not contain any macros.

More Tutorials

FIND

SEARCH

LEFT, RIGHT, MID

SUBSTITUTE

Functions List