How to Extract Numeric Digits from Text in Excel?
Struggling to isolate numbers from a text string? This formula will help you extract only the numeric digits efficiently.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Extract only the numeric digits from a cell that contains a mix of text and numbers"
=TEXTJOIN("", TRUE, IF(ISNUMBER(MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1), 1, 1), MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1), ""))This formula extracts all numeric digits from a mixed text string in a specified cell.
Step-by-step breakdown
- Use ROW and INDIRECT to create an array of each character's position in the string.
- MID function retrieves each character based on the array created.
- ISNUMBER checks if the retrieved character is a numeric digit.
- TEXTJOIN concatenates all numeric digits into a single string, ignoring non-numeric characters.
Edge cases & warnings
- Input string contains no numeric digits, resulting in an empty output.
- Input string is entirely numeric, returning the same number.
- Input string has leading or trailing spaces affecting the output.
- Input string contains special characters mixed with numbers.
Examples
"abc123def456"
123456"item#42 is available"
42Frequently asked questions
Can this formula handle decimal numbers?
No, this formula only extracts whole numeric digits.
What if my text contains letters only?
The formula will return an empty string if there are no numeric digits.
Is there a limit to the length of the input string?
Excel has a character limit of 32,767 characters for a single cell.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.