Function Problems

How to Split Variable Street Addresses into Columns in Excel

Natalie Taylor Oct 19, 2026 868 views

Question details

The user needs to separate inconsistently formatted addresses containing house numbers, multi-word street names, and apartment labels into distinct Excel columns.

How to Split Variable Street Addresses into Columns in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Organizing raw address data into structured columns when standard delimiters like a single space fail due to varying address lengths and components.
Observed behavior: Simple space delimiters or fixed-position formulas place multi-word street names or apartment information into the wrong columns because the address structures vary significantly.

Before you start

Verify that your version of Excel supports Dynamic Array functions like TEXTSPLIT, TEXTBEFORE, and TEXTAFTER (available in Microsoft 365 and Excel 2021), or prepare to use Power Query for older versions.

Solution 1Recommended

Use the TEXTSPLIT Function for Basic Extraction

A quick method to divide address components by a space delimiter, suitable for initial data separation.

Excel does not have a SPLIT function like Google Sheets; instead, it uses TEXTSPLIT. While this function is fast, keep in mind that it will split every single word into its own column. This means multi-word street names will span across multiple columns and may require further manual cleanup.

Select a destination cell

Click on a blank cell adjacent to your first address (for example, B1 if your address is in A1).

Enter the TEXTSPLIT formula

Type the formula =TEXTSPLIT(A1, " ") to instruct Excel to split the text wherever there is a space.

Execute the function

Press Enter. The address components will automatically spill across the neighboring columns.

Use the TEXTSPLIT Function for Basic Extraction

Formatting Limitations: This method places every word into a separate column and cannot reliably distinguish multi-word street names from apartment or unit information automatically.

Solution 2

Extract Specific Components Using TEXTBEFORE and TEXTAFTER

Use targeted text functions to pull out specific parts, like the house number or unit labels, based on structural rules.

Solution 3

Standardize Addresses with Power Query

The most reliable built-in method for parsing highly inconsistent address structures without writing complex nested formulas.

Efficient Data Processing with WPS Office

Clean and Split Address Data in WPS Spreadsheet

WPS Office Spreadsheet provides powerful text functions and intelligent tools like Flash Fill to easily parse, format, and structure complex address lists without heavy formula writing.

1. Open your address list: Launch WPS Spreadsheet and open the document containing your variable street addresses.
2. Use the Text to Columns tool: Highlight your data, navigate to the Data tab, and select "Text to Columns" to split the addresses using spaces or commas as delimiters.
3. Leverage Flash Fill for smart extraction: Alternatively, type the desired output (like just the house number or just the street name) in the adjacent cell, select the column below it, and press Ctrl+E. WPS will detect the pattern and fill the rest.
4. Save your structured data: Save your organized columns directly as an XLSX file, ensuring perfect compatibility.

Fully compatible with Microsoft Excel file formats (XLSX, CSV).Includes intelligent data extraction tools like Flash Fill for quick address parsing.Provides intuitive Text to Columns wizards for visual data separation.Free, lightweight, and features a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Does Excel have a SPLIT function like Google Sheets?

No, Excel does not use the SPLIT function found in Google Sheets. Instead, newer versions of Excel (Microsoft 365 and Excel 2021) use the TEXTSPLIT function to achieve the same result.

How can I separate address parts without using formulas?

You can use the Flash Fill feature. Simply type the house number in the cell next to the first address and press Ctrl+E. Repeat this in the next column for the street name. Excel will recognize your pattern and fill the rest of the rows automatically.

Why does TEXTSPLIT put multi-word street names into different columns?

TEXTSPLIT separates text based strictly on the delimiter you provide. If you use a space as the delimiter, it cannot differentiate between a space separating a house number from a street and a space between words in a street name (like 'Main Street').

What is the best way to handle highly inconsistent addresses?

For highly variable data, using Power Query or a dedicated third-party address-parsing service is highly recommended over basic Excel formulas, as they can apply structured logic and recognize standard address formats.

Excel & Data Problems