Split Address With Formulas

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.

Split Address Without Formulas

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.

• Flash Fill to split addresses based on a pattern
• Text to Columns for comma-separated address fields

Results Do Not Update

With these two non-formula methods, the data is separated into columns, and is no longer connected to the original full address.

• If the full address is updated, the separated fields do not update automatically
• You would need to manually update the separated fields individually

When to Use Non-Formula Methods

These solutions are quick and easy, if you don't plan to change the "full address" cell in the future.

For example,

• The data was imported from a website, or a reporting system
• To send out a mailing, you need a one-time fix to separate the data into columns
• Any address changes would not be made in your copy of the list

Flash Fill to Split Address

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:

• Hyphen separates street address and city name - yellow highlight in screen shot
• Comma separates city name and state code - green highlight
• Space character separates state code and zip code - pink highlight

Split Address with Flash Fill

Follow these steps to split the address with a Flash Fill:

1. Manually enter address field items in first row, in separate columns. This shows Excel what the data pattern is, so it can split the remaining addresses based on that pattern
   • TIP: For zip code, type apostrophe (') at start of number, to prevent loss of leading zero
2. Select first blank cell in next row
3. Press Ctrl+E to flash fill
4. Press Tab, to move to next column
5. Use Ctrl+E in all remaining columns, to flash fill

The Excel Flash Fill completes the entries in all of the columns, based on the manual entries in the first row.

Video: Flash Fill Shortcut

In this short video, Sarah shows how to separate data from one cell, into multiple columns, using the Flash Fill Shortcut - Ctrl+E

Text to Columns for Comma Separated

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.

• The comma separators are used consistently, in every full address.
• The commas are highlighted in green, in a couple of the records shown below.

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.

Split Address With Text to Columns

Follow these steps to split the address with the Text to Columns feature:

• Select all the full addresses in column A
• On the Excel Ribbon, click the Data tab
• Click the Text to Columns command

Convert Text to Columns Wizard

The Convert Text to Columns Wizard opens, showing Step 1 of 3

• For the Original data type, select the Delimited option
• Click Next

In Step 2 of 3, you'll set the delimiters for your data:

• In Step 2, for Delimiters, add a check mark to Comma
• Remove any other check marks
• Preview pane shows how the address will split into columns

In Step 3 of 3, you'll set the final details for your data:

• In Step 3, in the Preview pane, click the heading for the Zip Code column
• In the Column Data Format section, click the radio button for Text
  • This will prevent any leading zeros from being dropped from the Zip Code
• Click in the Destination box, and then click on cell B4, where the first street address should go
• Click Finish, to split the data into separate columns

The Excel Text to Columns feature splits each address into columns, and the full address remains in column A.

Split Address With Formulas

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.

• The comma separators are used consistently, in every full address.
• The commas are highlighted in green, in a couple of the records shown below.

To split the Street Address, City, State and Zip Code into separate columns, you can use Excel formulas - see the details below.

Results Do Update

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.

• If the full address is updated, the separated fields update automatically
• You do not need to manually update the separated fields individually

When to Use Formula Method

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,

• The data was imported from a website, or a reporting system
• Any address changes will be made in your copy of the list, in the full address column

Formulas to Find the Commas

In each full address, there are 3 comma separators:

1. After the street address
2. After the City name
3. After the State code

To find the position of each comma, 3 new columns will be added to the table.

Named Cell for Separator

First, a named range is created on the worksheet in cell F1.

• The cell is named mySep, and a comma is typed in that cell.
  • For steps on naming a cell, go to the Named Ranges page

This named cell makes it easy to do a couple of things:

• refer to the cell in the worksheet formulas
• change the separator character later, if needed, without changing any of the formulas

Comma Formulas

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.

• Cm1, in cell F4: =FIND(mySep,A4)
• Cm2, in cell G4: =FIND(mySep,A4,F4+1)
• Cm3, in cell H4: =FIND(mySep,A4,G4+1)

All 3 formulas use the FIND function, with these 2 arguments:

1. find_text: look for the separator character (mySep)
2. within_text: in the full address in cell A4

The FIND function has an optional 3rd argument -- start_num

1. For cm1, the 3rd argument is omitted, so find starts from the first character
2. For cm2, the start number is the position of the 1st comma (in F4), plus 1
3. For cm3, the start number is the position of the 2nd comma (in G4), plus 1

Street Address Formula

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.

City Formula

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:

• text: the full address (A4)
• start_num: starting position of the city name is the position of the first comma (F4), plus 1
• num_chars: the number of characters in the city name is calculated from the position of the second comma (G4), minus the position of the first comma (F4), minus 1

State Formula

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:

• text: the full address (A4)
• start_num: starting position of the city name is the position of the 2nd comma (G4), plus 1
• num_chars: the number of characters in the city name is calculated from the position of the 3rd comma (H4), minus the position of the 2nd comma (G4), minus 1

Zip Code Formula

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:

• number of characters in cell A4
• minus the position of the 3rd comma (in cell H4).

Excel Split Address Challenge

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.

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.

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:

• =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.
  • Or, use the Flash Fill shortcut - Ctrl + E
• Repeat for cells D6, E6 and F6.

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

Download Sample Files

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.

More Tutorials

FIND

SEARCH

LEFT, RIGHT, MID

SUBSTITUTE

Functions List