Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to extract every number from a text string in Excel and Google Sheets
Topic:Text manipulationHeads up: Excel and Google Sheets do this differently.
Excel
=IFERROR(VALUE(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),(SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)-1)*100+1,100))),"")Google Sheets
=IFERROR(REGEXEXTRACT(A2, "(\d+(?:\.\d+)?)"), "")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Text |
| Order 42 shipped 3 items |
| Temperature: 98.6 degrees |
| Ref 1001 batch 7 |
| Price $12.50 qty 4 |
=IFERROR(VALUE(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),(SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1)-1)*100+1,100))),"")→
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1In Google Sheets, enter =IFERROR(REGEXEXTRACT(A2, "(\d+(?:\.\d+)?)"), "") in B2 to extract the first integer or decimal from A2.
- 2For all numbers in Sheets, wrap in ARRAYFORMULA or use =REGEXEXTRACT(A2, "(\d+(?:\.\d+)?)") — Sheets returns the first match by default; wrap in a helper column with successive REGEXREPLACE to strip already-found matches for additional numbers.
- 3In Excel 365, the SUBSTITUTE + REPT approach space-pads around each word, then MID extracts 100-char chunks; VALUE converts numeric chunks and IFERROR suppresses text chunks.
- 4For a simpler Excel approach, use Power Query: Data → Get Data → From Table, then add a Custom Column with Text.Select([Column], {"0".."9","."}).
- 5Validate results by checking that VALUE() of extracted strings returns a number — if the result is text, wrap with VALUE().
Tips
- REGEXEXTRACT only extracts the first match unless you use it iteratively or use REGEXEXTRACT with multiple capture groups for a known count of numbers.
- In Excel, the SUBSTITUTE/REPT trick only works reliably when numbers are separated by spaces; for numbers run together with text (e.g. '3kg'), Power Query or a custom LAMBDA is cleaner.
- Decimal numbers with commas as the decimal separator need a different regex — adjust \d+(?:\.\d+)? to \d+(?:[,.]\d+)? for European locales.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I extract ALL numbers from a cell in Google Sheets (not just the first)?
Use =SPLIT(REGEXREPLACE(A2, "[^0-9.]+", " "), " ", TRUE, TRUE) to strip non-numeric characters and split on spaces — each number lands in its own cell.
Does the Excel formula handle decimal numbers?
The SUBSTITUTE/REPT approach works on space-separated tokens, so decimals like '3.14' are extracted whole if they are surrounded by spaces. Decimals embedded in words (e.g. '3.14kg') require Power Query or a LAMBDA-based character-by-character scan.
More on Text manipulation
See all →How-to
How to split text into columns
Split a full name or any delimited text into separate columns. Google Sheets uses SPLIT; Excel uses TEXTSPLIT — here is each.
How-toHow to remove extra spaces
Clean leading, trailing, and double spaces with TRIM — the usual fix for lookups that fail on values that look identical.
How-toHow to convert text to a number
Turn numbers stored as text into real numbers with VALUE or by multiplying by 1 — the fix for sums that ignore values.
How-toHow to extract the domain from an email address
Pull everything after the @ from an email using FIND and MID. Works the same in Excel and Google Sheets.
How-toHow to extract the first name from a full name
Get the first name from a full name with LEFT and FIND, splitting on the first space. Works in Excel and Google Sheets.
How-toHow to add leading zeros
Pad numbers to a fixed width with leading zeros using TEXT, or a custom number format. Works in Excel and Google Sheets.