How to split an address into separate columns in Google Sheets ?

To turn an address into separate columns in Google Sheets, you can use the "Split text to columns" feature. Here's a step-by-step guide:

1. Open Google Sheets: Open the Google Sheets document that contains the addresses you want to split.

2. Select the Cells: Highlight the cells that contain the addresses you want to split. If the addresses are in a single column, click on the column header to select the entire column.

3. Data Menu: Go to the top menu and click on Data.

4. Split Text to Columns: In the dropdown menu, select Split text to columns.

5. Separator: A small menu will appear at the bottom of the selected cells. Click on the dropdown arrow in this menu to choose the separator. Common separators include:
   • Comma: If your addresses are separated by commas (e.g., "123 Main St, Springfield, IL, 62701").
   • Space: If your addresses are separated by spaces.
   • Custom: If your addresses use a different separator, you can specify it here.

6. Adjust Columns: Google Sheets will automatically split the text into separate columns based on the chosen separator. You may need to adjust the column widths to see all the data clearly.

Example

Suppose you have the following address in cell A1:

123 Main St, Springfield, IL, 62701

After selecting the cell and using the "Split text to columns" feature with a comma as the separator, the data will be split into four columns:

• Column A: 123 Main St
• Column B: Springfield
• Column C: IL
• Column D: 62701

Additional Tips

• Manual Adjustments: If the automatic split doesn't work perfectly, you can manually adjust the data in the new columns.
• Formulas: For more complex splitting, you can use formulas like SPLIT(), LEFT(), RIGHT(), and MID() to extract specific parts of the address.

Edge Cases

While the "Split text to columns" feature in Google Sheets is quite powerful, there are certain edge cases where it may not work perfectly. Here are some scenarios and how specialized software can help:

Inconsistent separators

Inconsistent separators can pose a challenge when splitting addresses. For instance, if your dataset includes addresses that use different separators like commas and spaces, the "Split text to columns" feature may not split the data correctly.

If you encounter inconsistent separators in your dataset and need to manually fix them before using the "Split text to columns" feature, you can follow these steps:

Step-by-Step Guide to Manually Fix Inconsistent Separators

1. Identify Inconsistent Separators: First, scan through your dataset to identify the different types of separators used (e.g., commas, spaces, semicolons).

2. Use Find and Replace: Use the "Find and Replace" feature in Google Sheets to standardize the separators.

   • Open Find and Replace: Press Ctrl + H (Windows) or Cmd + H (Mac) to open the Find and Replace dialog.
   • Find: Enter the inconsistent separator you want to replace (e.g., a space or semicolon).
   • Replace with: Enter the standard separator you want to use (e.g., a comma).
   • Replace All: Click on "Replace All" to replace all instances of the inconsistent separator with the standard one.

3. Repeat for Other Separators: If there are multiple types of inconsistent separators, repeat the Find and Replace process for each one.

Example

Suppose you have the following addresses with inconsistent separators:

123 Main St, Springfield IL; 62701
456 Elm St; Shelbyville, IN 46176

You want to standardize them to use commas as separators.

1. Find and Replace Semicolons:

   • Open the Find and Replace dialog (Ctrl + H or Cmd + H).
   • Find: ;
   • Replace with: ,
   • Click "Replace All".

2. Find and Replace Spaces (if needed):

   • Open the Find and Replace dialog again.
   • Find: (space)
   • Replace with: ,
   • Click "Replace All".

After standardizing the separators, your addresses should look like this:

123 Main St, Springfield, IL, 62701
456 Elm St, Shelbyville, IN, 46176

Now you can use the "Split text to columns" feature with a comma as the separator to split the addresses into separate columns.

Additional Tips

• Preview Changes: Before clicking "Replace All," you can click "Find" to preview the changes and ensure they are correct.
• Backup Data: It's a good practice to create a backup of your data before making bulk changes, in case you need to revert to the original dataset.
• Regular Expressions: For more complex replacements, you can use regular expressions in the Find and Replace dialog by checking the "Search using regular expressions" option.

By manually standardizing the separators, you can ensure that the "Split text to columns" feature works effectively, even with initially inconsistent data.

Complex addresses

Complex addresses, such as those with multiple lines or additional information like apartment numbers and PO boxes, can be difficult to split correctly. Processing complex addresses in Google Sheets can be challenging due to the presence of multiple lines, additional information like apartment numbers, PO boxes, and varying formats. Here are some advanced techniques and tools you can use to handle complex addresses effectively:

Using Formulas for Complex Addresses

Using the SPLIT() Function

The SPLIT() function can be used to divide text into separate columns based on a specified delimiter. For complex addresses, you might need to use multiple SPLIT() functions or combine them with other text functions.

Example:

Suppose you have the following complex address in cell A1:

123 Main St Apt 4B, Springfield, IL, 62701

You can use the SPLIT() function to separate the address into different components:

=ARRAYFORMULA(SPLIT(A1, ","))

This will split the address into:

• Column A: 123 Main St Apt 4B
• Column B: Springfield
• Column C: IL
• Column D: 62701

Using LEFT(), RIGHT(), and MID() Functions

For more granular control, you can use the LEFT(), RIGHT(), and MID() functions to extract specific parts of the address.

Example

To extract the street address, city, state, and ZIP code separately, you can use:

• Street Address:

  =LEFT(A1, FIND(",", A1) - 1)

• City:

  =MID(A1, FIND(",", A1) + 2, FIND(",", A1, FIND(",", A1) + 1) - FIND(",", A1) - 2)

• State:

  =MID(A1, FIND(",", A1, FIND(",", A1) + 1) + 2, 2)

• ZIP Code:

  =RIGHT(A1, 5)

Using Regular Expressions

Regular expressions (regex) can be used for more complex text parsing. Google Sheets supports regex through the REGEXEXTRACT() function.

Example:

To extract the ZIP code from a complex address:

=REGEXEXTRACT(A1, "\d{5}$")

Specialized Software

For cases where you have a lot of different formatting within the same files, the use of specialized software like Address Extractor can help save a lot of time - especially if you have to do it several times a day: Try Address Extractor for 7 days for free (then $49/m) - no credit card required