How to split an address into columns in Google Sheets

1. Split text to columns

For a one-time job on tidy addresses, the menu is fastest:

1. Make sure the columns to the right of the addresses are empty: the split writes over them.
2. Select the address column and choose Data → Split text to columns.
3. In the Separator box that appears, choose Comma. You get street, city and “IL 62701”.
4. Select the state-and-ZIP column and split it again, with Space as the separator.
5. Check the ZIP codes. Sheets may turn 02108 into the number 2108; fix that with =TEXT(D2, "00000") in a new column.

The catch: it splits at every comma. “418 Alder Ln, Apt 2, Eugene, OR 97401” has one comma more than the other rows, so its city lands in the state column, and you won’t notice unless you look.

2. The SPLIT formula

The formula version keeps the original column and updates when the address changes. In B2:

=ARRAYFORMULA(TRIM(SPLIT(A2, ",")))

TRIM removes the space after each comma. To take one part only, wrap it in INDEX: =INDEX(TRIM(SPLIT(A2, ",")), 1, 2) gives the second part, the city. It has the same problem as the menu with extra commas, and the same leading-zero problem with ZIP codes.

3. One formula for messy addresses

When the number of commas varies, match the address from the end instead, where US addresses are predictable: state, then ZIP code. Put this in B2 and copy it down. It fills four columns (street, city, state and ZIP code):

=IFERROR(REGEXEXTRACT(A2, "^\s*(.+?),\s*([^,]+?),\s*([A-Z]{2}),?\s+(\d{5}(?:-\d{4})?)(?:,\s*(?:USA|US|United States))?\s*$"), "CHECK")

We tested it on these addresses before publishing:

How it works: the city is the last comma-separated part before a two-letter state, and everything before that is the street, however many commas it has. The ZIP code can have a ZIP+4 ending, and a trailing “USA” is ignored. Because REGEXEXTRACT returns text, 02109 keeps its zero.

Rows it can’t read show CHECK: no commas at all (“Main St Springfield IL 62701”), a spelled-out state, or a non-US address. Filter column B for CHECK to see them all. If some states are in lower case, use UPPER(A2) instead of A2 in the formula.

Pull out single parts

Each of these works on its own, on a full address in A2 (tested on the same addresses):

And on a street in B2:

The unit formula needs a digit in the unit, so a street such as “200 Unit Way” isn’t mistaken for one. Wrap any of these in IFERROR(…, "") to leave a blank when there’s no match.

Awkward addresses

Real lists are rarely as neat as the examples above. These fixes cover the cases that make the main formula say CHECK. We tested each pattern in a re-implementation of Google’s regular expressions on the example addresses.

Addresses on several lines. Addresses copied from labels or emails often have line breaks inside the cell. Turn them into commas first, then split as usual:

=SUBSTITUTE(A2, CHAR(10), ", ")

Or put each line in its own column with =SPLIT(A2, CHAR(10)).

Odd spaces. Text pasted from web pages can contain non-breaking spaces, which look like spaces but don’t match \s in a regular expression and aren’t removed by TRIM. Clean them first:

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

No commas at all. In “123 Main St Springfield IL 62701” nothing marks where the street ends and the city starts, and no formula can know that “Main St Springfield” isn’t a street name. You can still take the state and ZIP code reliably, into two columns:

=IFERROR(REGEXEXTRACT(A2, "\b([A-Z]{2})\s+(\d{5}(?:-\d{4})?)\s*$"), "")

For the street and city, a geocoder is the dependable route: it returns each part separately (see the last section).

Spelled-out states. This version of the main formula accepts “Oregon” or “West Virginia” as well as “OR”, and still fills four columns:

=IFERROR(REGEXEXTRACT(A2, "^\s*(.+?),\s*([^,]+?),\s*([A-Za-z][A-Za-z .]*?),?\s+(\d{5}(?:-\d{4})?)(?:,\s*(?:USA|US|United States))?\s*$"), "CHECK")

To turn the names into two-letter codes, make a States tab with full names in column A and codes in B (the Postal Service publishes the list), then, with the state in D2:

=IF(LEN(TRIM(D2))=2, UPPER(TRIM(D2)), XLOOKUP(TRIM(D2), States!$A$1:$A$60, States!$B$1:$B$60, "CHECK"))

It keeps codes that are already two letters (in capitals), looks up full names whatever their case, and shows CHECK for anything else, such as the old abbreviation “Mo.”.

PO boxes. To flag or pull out a PO box, which the Postal Service handles differently from a street address:

=IFERROR(REGEXEXTRACT(A2, "(?i)\bP\.?\s*O\.?\s*Box\s+\d+"), "")

It matches “PO Box 123”, “P.O. Box 77” and “p o box 5”, but not a street such as “12 Boxwood Ln”.

Canadian and UK postcodes. The US formulas look for five digits, so they skip these. To pull the postcode from the end of the address:

Both check the shape of the code, not whether it exists. The rest of a non-US address varies too much by country for one formula; geocoding handles it better.

Join them back together

The reverse, for address labels or a geocoding tool that wants one column:

=TEXTJOIN(", ", TRUE, B2, C2, D2&" "&E2)

TRUE skips empty parts, so a missing street doesn’t leave a stray comma.

When formulas aren’t enough

Formulas can only cut up the text that’s there. They can’t fill in a missing ZIP code, fix “Sprigfield”, find the county, or split addresses from countries with other formats. That needs a geocoder, which matches the address against map data and returns its parts. The US Census Bureau’s free Census Geocoder does this for US addresses, including county as part of its census geography results, and accepts a file of addresses at a time.

Troubleshooting

• #REF! with “Array result was not expanded”. SPLIT and the four-column REGEXEXTRACT spill to the right, and a cell in the way stops them. Clear the columns next to the formula.
• ZIP codes lost their leading zero. Data → Split text to columns and SPLIT turn 02109 into the number 2109. Use the REGEXEXTRACT formulas, which return text, or rebuild it with =TEXT(E2, "00000").
• “Function REGEXEXTRACT parameter 1 expects text values”. The cell holds a number, for example a ZIP code on its own. Use A2&"" instead of A2 to turn it into text first.
• Everything comes back CHECK. The states are in lower case (wrap the address in UPPER()), the addresses have no commas, or they span several lines. See the fixes above.
• The city keeps a stray space or the street has a trailing comma. Wrap the result in TRIM(), or use the regex formula, which trims as it matches.

Frequently asked questions

Related

• How to get the ZIP code from an address in Google Sheets
• How to geocode addresses in Google Sheets
• How to calculate the distance between ZIP codes in Google Sheets
• How to map addresses from Google Sheets
• MapRows documentation
• All guides

Disclosure: we make MapRows, the Google Sheets add-on mentioned in this guide. Every formula here works without it.

Google Sheets is a trademark of Google LLC. MapRows and Cellbrook are not affiliated with or endorsed by Google. ZIP Code is a trademark of the United States Postal Service.