How to Extract Numbers and Initials from an Address in Excel
Question details
The user needs to convert full addresses into compact alphanumeric codes by extracting the house number and combining it with the initials of the street name.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Generating compact identification codes from a list of addresses with varying house-number lengths and street name word counts.
- Observed behavior
- Addresses like '2 Birch Court' need to be automatically parsed and reformatted into standardized codes like '0002BC' without manual entry.
Ensure your version of Excel supports dynamic array functions like TEXTSPLIT and LET, which are typically available in Microsoft 365 and newer versions.
Use Dynamic Array Functions (LET and TEXTSPLIT)
This is the most reliable formula method for Microsoft 365 users, splitting the text by spaces to process the house number and street words separately.
This formula uses TEXTSPLIT to break the address into separate words. It then takes the first part (the number), formats it to four digits with the TEXT function, and extracts the first letter of the remaining words using LEFT and DROP.
Click on the empty cell where you want the compact code to appear.
Type the formula: =LET(parts,TEXTSPLIT(A1," "),CONCAT(TEXT(TAKE(parts,,1),"0000"),LEFT(DROP(parts,,1)))) (assuming your source address is in cell A1).
Press Enter to generate the code. The formula automatically pads the number with leading zeros and appends the initials of the remaining words.
Click the small square at the bottom-right corner of the cell (fill handle) and drag it down to apply this formula to other addresses in your column.
Extract Using REGEX Functions
If your spreadsheet software or specific Excel version supports regular expressions, you can extract numbers and letters using pattern matching.
Extract Text and Numbers Easily in WPS Spreadsheet
WPS Spreadsheet provides powerful text manipulation functions and intelligent tools like Flash Fill to handle complex data extraction effortlessly.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open the workbook containing your addresses.
- 2. Type the desired pattern: In the blank cell adjacent to your first address (e.g., next to '2 Birch Court'), manually type the target code '0002BC' and press Enter.
- 3. Use Flash Fill for quick extraction: Select the cell below the one you just typed in and press the shortcut Ctrl+E. WPS Spreadsheet will automatically detect the pattern and fill the rest of the column instantly.
Frequently Asked Questions
What if my address doesn't start with a number?
The provided formulas assume the address begins with a number separated by a space. If the number is embedded or missing, the TAKE function will grab the first word instead, and the TEXT formatting may result in an error. For highly irregular addresses, using Flash Fill (Ctrl+E) or a more complex REGEX pattern is recommended.
Can I use Flash Fill instead of formulas to extract initials?
Yes. You can manually type the desired output (like '0002BC') in the cell next to '2 Birch Court' and press Ctrl+E. Flash Fill will attempt to recognize the extraction and formatting pattern and apply it to the rest of the column, which often works perfectly for consistent address formats.
Why do I get a #NAME? error when using TEXTSPLIT?
The #NAME? error occurs if your version of Excel does not support the TEXTSPLIT, TAKE, or DROP functions. These are relatively new dynamic array functions available primarily in Microsoft 365. If you use Excel 2019 or older, you will need to rely on Flash Fill or complex combinations of LEFT, MID, FIND, and SUBSTITUTE.
