How to Extract Numbers from Text
This article explains how to extract numbers from text in Excel. This is especially useful for mixed data (e.g., “Product123”) where only the numeric portion is needed. Multiple methods are discussed below.
Using TEXTJOIN and MID Functions to Extract Numbers
| A | B | |
|---|---|---|
| 1 | Original Text | Extracted Numbers |
| 2 | Product123 | =TEXTJOIN(“”, TRUE, IF(ISNUMBER(MID(A2, ROW(INDIRECT(“1:”&LEN(A2))), 1) * 1), MID(A2, ROW(INDIRECT(“1:”&LEN(A2))), 1), “”)) |
| 3 | ABC456Product | =TEXTJOIN(“”, TRUE, IF(ISNUMBER(MID(A3, ROW(INDIRECT(“1:”&LEN(A3))), 1) * 1), MID(A3, ROW(INDIRECT(“1:”&LEN(A3))), 1), “”)) |
Steps
- Prepare the cells containing the target strings in column A.
- Use the formula in column B to extract numbers:
- For example, in B2, enter:
=TEXTJOIN(“”, TRUE, IF(ISNUMBER(MID(A2, ROW(INDIRECT(“1:”&LEN(A2))), 1) * 1), MID(A2, ROW(INDIRECT(“1:”&LEN(A2))), 1), “”)).
Function Details
TEXTJOIN combines multiple characters, while MID extracts specific characters from the text. ISNUMBER checks if the extracted character is a number. This combination extracts only numeric values.
Results
For A2 (“Product123”), B2 displays 123. Similarly, for A3 (“ABC456Product”), B3 shows 456.
Using TEXTJOIN and IFERROR Functions to Extract Numbers
| A | B | |
|---|---|---|
| 1 | Original Text | Extracted Numbers |
| 2 | Product789 | =TEXTJOIN(“”, TRUE, IFERROR(MID(A2, ROW(INDIRECT(“1:”&LEN(A2))), 1) * 1, “”)) |
| 3 | DEF123Product | =TEXTJOIN(“”, TRUE, IFERROR(MID(A3, ROW(INDIRECT(“1:”&LEN(A3))), 1) * 1, “”)) |
Steps
- Enter text into column A.
- Use the formula with TEXTJOIN and IFERROR in column B to extract numbers.
- For instance, in B2, enter:
=TEXTJOIN(“”, TRUE, IFERROR(MID(A2, ROW(INDIRECT(“1:”&LEN(A2))), 1) * 1, “”)).
Function Details
TEXTJOIN combines extracted numbers, while IFERROR handles errors by replacing them with blank values. This effectively filters out non-numeric characters.
Results
For A2 (“Product789”), B2 displays 789. Similarly, for A3 (“DEF123Product”), B3 shows 123.
Conclusion
To extract numbers from text in Excel, you can use combinations of TEXTJOIN and MID functions, or TEXTJOIN and IFERROR. Choose the method that best fits your needs for efficient extraction of numeric values from text strings.