Text
Extract Numbers from Text
Extract numeric digits from a text string that contains a mix of letters and numbers. Useful for pulling amounts, IDs, or quantities from unstructured text.
Excel Formula
=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),0),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)Google Sheets Version
=VALUE(REGEXEXTRACT(A2,"[0-9]+"))Step-by-Step Explanation
1
This complex formula extracts all digits from text
2
In Google Sheets, REGEXEXTRACT makes this much simpler
3
It examines each character to check if it's a number
4
Digits are assembled back into a number
5
For simpler needs, consider the Google Sheets version
Example
| Text (A) | Number (B) |
|---|---|
| Order #12345 | 12345 |
| Total: $1,299 | 1299 |
| Room 42B | 42 |
Result: Google Sheets: =VALUE(REGEXEXTRACT(A2,"[0-9]+")) → 12345
Common Variations
Google Sheets (all numbers)
=REGEXEXTRACT(A2,"[\d.]+")Includes decimals
Extract first number
=VALUE(REGEXEXTRACT(A2,"\d+"))Sheets: first number only
Need a Custom Version?
Use our AI generator to create a formula tailored to your specific data and requirements.
Try It with AI →