Function Problems

How to Extract Street Names from Address Cells in Excel

Maira Mehtab Sep 27, 2026 871 views

Question details

The user needs to extract only the street name from complex address cells that contain property numbers, street names, country names, postal codes, and separators.

Product: Excel
Device & OS: not provided
Scenario: Cleaning and standardizing address data for mailing lists or databases by isolating specific text strings.
Observed behavior: Address cells currently contain mixed data (e.g., "52 Stevens Road · 257848"), requiring the removal of digits and separators to isolate just the street name.

Before you start

Check which version of Excel you are using, as regular expression functions are only available in the latest Microsoft 365 updates, whereas Power Query is available in most modern versions.

Solution 1Recommended

Use Power Query to Clean Address Data

Power Query is the most reliable method across modern Excel versions to strip numbers and specific characters from text without complex nested formulas.

Power Query allows you to add custom columns using M code functions like Text.Remove, Text.Clean, and Text.Trim to process and sanitize your text strings.

Load data into Power Query

Select your address data, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Remove digits using Text.Remove

Go to 'Add Column' > 'Custom Column'. Enter the formula: Text.Remove([ColumnName], {"0".."9"}) to strip out all numbers such as property and postal codes.

Clean and trim the text

Wrap your formula in Text.Trim(Text.Clean(...)) to remove unprintable characters and extra spaces left over after deleting the numbers.

Remove unwanted separators

Use the 'Split Column' feature by delimiter (such as the middle dot '·' or a comma) to separate and delete the remaining country or postal code sections. Finally, click 'Close & Load' to return the cleaned street names to Excel.

Solution 2

Extract Text using the REGEXEXTRACT Function

For users with the latest Microsoft 365 updates, regular expressions offer a powerful, one-formula solution to extract specific text patterns.

Smart Text Extraction

Use WPS Spreadsheet Flash Fill for Quick Text Extraction

WPS Office Spreadsheet provides a powerful, AI-assisted Flash Fill feature that recognizes patterns and extracts exactly what you need without complex formulas or Power Query setups.

1. Open your file: Open your address dataset in WPS Spreadsheet.
2. Provide an example: In the adjacent blank column, manually type the exact street name you want to extract for the first row (e.g., type 'Stevens Road' next to '52 Stevens Road · 257848').
3. Select the next cell: Click on the empty cell directly beneath the example you just typed.
4. Apply Flash Fill: Press Ctrl + E on your keyboard. WPS Spreadsheet will detect the pattern and automatically fill the rest of the column with the extracted street names.

Smart Flash Fill recognizes text patterns instantly with a simple shortcutFully compatible with Microsoft Excel (.xlsx) formatsFree to download and useLightweight application with a familiar, user-friendly interface

Frequently Asked Questions

Can I remove text using standard Excel formulas instead of Power Query?

Yes, you can combine the SUBSTITUTE, MID, LEFT, and FIND functions to isolate text, but this can become overly complex for inconsistent address formats. Flash Fill or Power Query is highly recommended for unstructured text.

Why isn't the REGEXEXTRACT function working in my Excel?

The REGEXEXTRACT function is a newer addition and is currently only available to Microsoft 365 Insiders and users on the latest update channels. Older versions like Excel 2016 or 2019 do not support native regex formulas.

How do I handle special separators like a middle dot (·) when extracting text?

In Power Query, you can use the 'Split Column' feature by selecting 'By Delimiter', choosing 'Custom', and pasting the middle dot symbol to split the text, allowing you to easily delete the unwanted portion.

Excel & Data Problems