How to Extract Only the Numbers from a Text String in Excel using the FILTERXML Function

Data cleanup in Microsoft Excel is notoriously difficult when numbers and text are merged into a single cell. If you have an address like “452 Baker Street”, a product code like “XYZ-8921”, or a messy log file entry, and you want to extract only the numerical digits, traditional Excel formulas fail. Functions like LEFT, RIGHT, and MID require the numbers to be in a predictable, fixed position, which is rarely the case.

While newer versions of Excel support Power Query for this task, writing a formula is often much faster. By using a highly unconventional trick combining the TEXTJOIN, MID, ROW, and FILTERXML functions, you can create a formula that instantly strips out all alphabetical characters and leaves only the numbers behind.

The Magic Formula

Assume the messy text you want to clean is located in cell A2. Copy and paste the following master formula into cell B2:

=CONCAT(IFERROR(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1) * 1, ""))

Note: If you are using an older version of Excel (2019 or earlier) that does not support Dynamic Arrays, you must press Ctrl + Shift + Enter after pasting this formula to execute it as an Array Formula. If you are using Microsoft 365, just press Enter.

How the Formula Works (Step-by-Step)

This formula looks intimidating, but it is actually performing a very logical, step-by-step breakdown of the cell’s contents.

1. ROW and INDIRECT: The ROW(INDIRECT("1:" & LEN(A2))) portion acts as a loop. It counts exactly how many characters are in your cell (e.g., 15 characters) and generates an array of numbers from 1 to 15.
2. MID: The MID function takes that array and chops the entire text string into individual, single characters. “ABC12” becomes {“A”, “B”, “C”, “1”, “2”}.
3. The Multiplier (* 1): This is the clever trick. The formula attempts to multiply every single character by 1. In Excel, if you multiply a number by 1, it remains a number. If you attempt to multiply a letter by 1, Excel throws a #VALUE! error.
4. IFERROR: The IFERROR function catches all those errors. If the character was a letter (and thus errored out), it replaces it with a blank space (""). The array now looks like: {“”, “”, “”, “1”, “2”}.
5. CONCAT: Finally, the CONCAT function glues the surviving array back together into a single, clean string. All the letters have been filtered out into nothingness, leaving only the digits behind.

Important Considerations

While this formula is incredibly powerful for extracting product IDs, invoice numbers, or street addresses, it has one specific limitation: it strips everything that isn’t a digit from 0 to 9.

This means it will also strip out decimal points (.) and negative signs (-). If cell A2 contains the price “$45.99”, this formula will output “4599”. It is best used for extracting whole integer IDs or phone numbers rather than formatting currency or precise accounting figures.