How to Split Full Addresses in Google Sheets

Table of Contents

1. How address splitting works
2. Prepare your address data
3. Method 1: Split text to columns
4. Method 2: Use the SPLIT formula
5. Method 3: Extract street, city, state, and ZIP code
6. Apply formulas to multiple rows
7. Handle irregular or international addresses
8. Common mistakes
9. Pro tips and best practices
10. Key takeaways
11. Frequently asked questions
12. Conclusion

A full address stored in one cell is convenient for viewing, but it is difficult to sort, filter, validate, or import into another system. Learning how to split full addresses in Google Sheets lets you separate street address, city, state, and ZIP code into clean columns that are easier to manage.

This guide covers three practical methods: the built-in Split text to columns tool, the SPLIT formula, and REGEXEXTRACT for structured U.S. addresses. You will also learn how to process multiple rows and troubleshoot inconsistent data.

How Address Splitting Works in Google Sheets

Address splitting means taking a value such as:

123 Main Street, Austin, TX 78701

and converting it into separate fields:

Street Address	City	State	ZIP Code
123 Main Street	Austin	TX	78701

The best method depends on how consistent your addresses are. A simple comma-separated list is easy to split. Mixed formats, missing commas, apartment numbers, or international addresses usually need more advanced formulas or manual review.

Before You Split Addresses: Prepare the Data

Make a copy of your sheet before changing anything. Then confirm that the full addresses are stored in one column, such as column A, and that the columns to the right are empty. Split formulas expand into neighboring cells and can overwrite existing values.

For the cleanest results, standardize each address in this format:

Street Address, City, State ZIP Code

Check a few rows for missing commas, extra spaces, duplicate country names, line breaks, or incomplete ZIP codes. You can remove unwanted spacing by selecting the range and choosing Data > Data cleanup > Trim whitespace.

Method 1: Use Split Text to Columns

The built-in tool is the fastest choice for a one-time cleanup when every address uses commas consistently. Google provides a Split text to columns guide for separating clearly defined text into multiple columns.

1. Select the cells containing the full addresses.
2. Open Data in the top menu.
3. Choose Split text to columns.
4. Open the separator menu and select Comma.
5. Review the new columns and rename their headers.

For the sample address, this creates three columns: street address, city, and a combined state-plus-ZIP field. You can then separate the final field with another split or a formula.

Best for: small lists, consistent comma-separated addresses, and one-time cleanup.

Limitation: the result is static. If the original address changes later, the split columns do not update automatically.

Method 2: Use the SPLIT Formula

The Google Sheets SPLIT function divides text around a delimiter and places each part into a separate cell. If the full address is in A2, enter this formula in B2:

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

This formula uses the comma as the delimiter. FALSE tells Sheets to treat the delimiter as a complete value, while TRUE removes empty results. TRIM cleans extra spaces around each part.

The formula returns:

• Column B: Street address
• Column C: City
• Column D: State and ZIP code

To extract the two-letter state code from D2, use:

=REGEXEXTRACT(TRIM(D2),"^[A-Z]{2}")

To extract the ZIP code, use:

=REGEXEXTRACT(D2,"\d{5}(?:-\d{4})?$")

Best for: sheets where the source address may change and the separated values should update automatically.

Method 3: Extract Street, City, State, and ZIP Code

For standard U.S. addresses, REGEXEXTRACT can capture all four components with one formula. Place this formula in B2:

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

The four capture groups return four columns:

1. Everything before the first comma becomes the street address.
2. The text between the first and second commas becomes the city.
3. A two-letter uppercase code becomes the state.
4. A five-digit or ZIP+4 value becomes the ZIP code.

This method is more precise than splitting every comma because it validates the expected structure at the same time. However, it assumes that the address follows the exact U.S. pattern shown above.

How to Apply the Formulas to Multiple Rows

For a short list, enter the formula in the first row and drag the fill handle downward. For a larger list, use separate array formulas so each output column fills automatically.

Street address in B2:

=ARRAYFORMULA(IF(A2:A="","",TRIM(REGEXEXTRACT(A2:A,"^([^,]+)"))))

City in C2:

=ARRAYFORMULA(IF(A2:A="","",TRIM(REGEXEXTRACT(A2:A,"^[^,]+,\s*([^,]+)"))))

State in D2:

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

ZIP code in E2:

=ARRAYFORMULA(IF(A2:A="","",REGEXEXTRACT(A2:A,"(\d{5}(?:-\d{4})?)$")))

Keep the output columns empty before adding these formulas. When you need fixed values for an export, copy the results and use Paste special > Values only.

How to Handle Irregular or International Addresses

No single formula can accurately parse every address format. Cities may contain commas, apartment details may appear in different positions, and international addresses do not share one universal state or postal-code pattern.

When your data is inconsistent:

• Create separate rules for each country or known format.
• Standardize country names before parsing.
• Keep apartment or suite information with the street address.
• Use an error column to flag rows that return #N/A.
• Manually review uncommon or incomplete records.

Address splitting is often one step in a larger data-cleaning workflow. After organizing location fields, you may also need to clean website data. This guide on How to Extract Domain Names From URLs in Google Sheets explains the next useful step.

Common Mistakes When Splitting Addresses

• Overwriting data: Split results need empty columns to the right.
• Assuming every comma has the same meaning: Suite information or business names may contain extra commas.
• Ignoring spaces: Use TRIM or the Trim whitespace tool.
• Using one U.S. formula for global data: Postal formats vary widely.
• Converting ZIP codes to numbers: Keep ZIP codes as text so leading zeros are preserved.
• Skipping error checks: A formula returning #N/A usually means the row does not match the expected pattern.

Pro Tips and Best Practices

• Add headers before entering formulas: Full Address, Street Address, City, State, and ZIP Code.
• Test formulas on 10 to 20 rows before applying them to the full dataset.
• Use data validation for state codes when users enter addresses manually.
• Store ZIP codes as plain text, especially in states where codes may start with zero.
• Keep the original full-address column for reference and auditing.
• Use helper columns rather than building one extremely complicated formula.

Key Takeaways

• Use Split text to columns for quick, one-time separation.
• Use SPLIT when you want results that update with the source cell.
• Use REGEXEXTRACT for structured U.S. addresses and precise field extraction.
• Standardize the data before parsing it.
• Review unmatched rows instead of assuming every address follows the same pattern.

Frequently Asked Questions

How do I split an address into separate columns in Google Sheets?

Select the address cells, choose Data > Split text to columns, and select Comma as the separator. For dynamic results, use the SPLIT formula instead.

What formula splits a full address in Google Sheets?

For a comma-separated address in A2, use =ARRAYFORMULA(TRIM(SPLIT(A2,",",FALSE,TRUE))). It separates each comma-delimited part into a new column.

How do I extract the city from a full address?

For the format Street, City, State ZIP, use =TRIM(REGEXEXTRACT(A2,"^[^,]+,\s*([^,]+)")). The formula returns the text between the first and second commas.

How do I separate the state and ZIP code?

Extract the state with =REGEXEXTRACT(D2,"^[A-Z]{2}") and the ZIP code with =REGEXEXTRACT(D2,"\d{5}(?:-\d{4})?$").

Can Google Sheets split addresses automatically for many rows?

Yes. You can copy formulas down or use ARRAYFORMULA with REGEXEXTRACT to process an entire column. Keep the destination columns empty so results can expand.

Why does my address formula return #N/A?

The source address does not match the pattern expected by the formula. Check for missing commas, country names, lowercase state codes, extra text after the ZIP code, or incomplete address components.

Conclusion

Knowing how to split full addresses in Google Sheets makes address lists easier to sort, validate, filter, and import. Start with Split text to columns for simple data, use SPLIT for live formulas, and choose REGEXEXTRACT when you need separate street, city, state, and ZIP fields.

Test the method on a small sample, preserve the original full-address column, and review any rows that do not match your expected format. Then apply the same workflow to the rest of your spreadsheet.

Try one of the formulas on your address list today, and bookmark WP Automation Guide for more practical Google Sheets automation tutorials.