Formula Errors

How to Reorder Street Numbers in Imported Excel Addresses

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to extract a specific number located after a hash (#) symbol in a string of imported address data and move it to the beginning of the text using an Excel formula.

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Cleaning and reformatting imported address data where the street number is misplaced or appended with a '#' symbol.
Observed behavior: The imported address is formatted inconsistently (e.g., '123 Excel Drive #456 Unit 78'), and the goal is to extract the number after the '#' and place it at the front (e.g., '456 Excel Drive').

Before you start

Ensure your imported address data follows a relatively consistent format with the target number clearly separated by a '#' symbol, as text extraction formulas rely on consistent delimiters.

Solution 1Recommended

Use TEXTBEFORE and TEXTAFTER Functions to Reorder the Address

This method uses modern Excel text manipulation functions to locate the '#' symbol, extract the number following it, and concatenate it to the front of the street name.

By nesting TEXTAFTER and TEXTBEFORE functions, you can isolate specific substrings based on delimiters like spaces and hash symbols. This allows you to dynamically reconstruct the address without manually editing each cell.

Locate your data

Identify the column containing the imported addresses. For this example, assume your first address is in cell C1.

Enter the text extraction formula

Click on a blank adjacent cell (e.g., D1) and enter the following formula: =TEXTBEFORE(TEXTAFTER(C1,"#")," ")&" "&LEFT(TEXTBEFORE(TEXTAFTER(C1," "),"#"),LEN(TEXTBEFORE(TEXTAFTER(C1," "),"#"))-1)

Apply the formula to the column

Press Enter to see the reformatted address. Select the cell again and double-click the fill handle (the small square at the bottom-right corner of the cell) to apply this formula down to the rest of your address list.

Function Availability: The TEXTBEFORE and TEXTAFTER functions are available in newer versions of Excel (such as Microsoft 365 and Excel 2021) and updated versions of WPS Office. If you encounter a #NAME? error, your version may not support these functions.

Efficient Data Formatting

Clean Address Data Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced text manipulation formulas like TEXTBEFORE and TEXTAFTER, making it incredibly easy to clean and reorder imported address data. You can perform these tasks quickly within its highly compatible, user-friendly interface.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the imported address data.
2. Enter the formula: Select the target blank cell and paste the text extraction formula to rearrange the address delimiters.
3. Drag to fill: Use the fill handle at the bottom right of the cell to drag and apply the formula to all other addresses in your list automatically.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports advanced text manipulation functions for complex data cleaning.Lightweight software with fast processing for large datasets.

microsoft office alternative - wps office

Frequently Asked Questions

What if my Excel version doesn't support TEXTBEFORE and TEXTAFTER?

If you are using an older version of Excel, you can use the Flash Fill feature (Ctrl + E) by manually typing the desired output in the first adjacent cell and letting Excel detect the pattern. Alternatively, you can use a combination of MID, FIND, and LEN functions to locate the '#' symbol and extract the text.

How do I handle addresses that don't have a '#' symbol?

The provided formula specifically looks for the '#' delimiter. If an address doesn't contain it, the formula will return an error. You can wrap your formula in an IFERROR function, like =IFERROR(your_formula, C1), to keep the original address text when the symbol is missing.

Can I use Power Query to reorder street numbers instead of formulas?

Yes, Power Query is excellent for cleaning complex addresses. You can use the 'Split Column by Delimiter' feature under the Data tab to split the address at the '#' and space characters, manually rearrange the resulting columns, and then merge them back together in your preferred order.

Excel & Data Problems