How to extract numbers from a text string in Excel
Excel formula
=--CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))In Excel 365, keep only the characters that are digits and join them: =--CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,"")). In Google Sheets use =VALUE(REGEXREPLACE(A2,"\D","")).
- Result on the example
- one result per row in
B2, filled down toB7 - Works in
- Microsoft 365 only
- Google Sheets
- Different formula, see below
See it work
The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.
How it works
MID(A2,SEQUENCE(LEN(A2)),1) splits the text into single characters. Multiplying by 1 turns digits into numbers and everything else into #VALUE!, which IFERROR replaces with "". CONCAT joins what is left ("4471") and the double minus turns that text into a number.
In Google Sheets, \D means "not a digit", so REGEXREPLACE deletes every other character in one step. Excel 365 has REGEXREPLACE too now.
=--CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))Result4471
A2"Order 4471 shipped"The value in A2.
LEN(A2)18LEN: Number of characters.- text
A2"Order 4471 shipped"
- text
SEQUENCE(LEN(A2)){18 rows × 1 col} 1, 2, 3, 4, …SEQUENCE: A grid of sequential numbers. Spills.- rows
LEN(A2)18
- rows
MID(A2,SEQUENCE(LEN(A2)),1){18 rows × 1 col} "O", "r", "d", "e", …MID: Characters from the middle of text.- text
A2"Order 4471 shipped" - start_num
SEQUENCE(LEN(A2)){18 rows × 1 col} 1, 2, 3, 4, … - num_chars
1
- text
MID(A2,SEQUENCE(LEN(A2)),1)*1{18 rows × 1 col} #VALUE!, #VALUE!, #VALUE!, #VALUE!, …The * operator multiplies.
IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""){18 rows × 1 col} "", "", "", "", …IFERROR: Returns a fallback when the value is any error.- value
MID(A2,SEQUENCE(LEN(A2)),1)*1{18 rows × 1 col} #VALUE!, #VALUE!, #VALUE!, #VALUE!, … - value_if_error
""not used
- value
CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))"4471"CONCAT: Joins text and ranges.- text1
IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""){18 rows × 1 col} "", "", "", "", …
- text1
-CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))-4471Makes the value negative.
--CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))4471Makes the value negative.
In Google Sheets
Google Sheets needs a different formula: =VALUE(REGEXREPLACE(A2,"\D","")). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =LET(c,MID(A2,SEQUENCE(LEN(A2)),1),--CONCAT(FILTER(c,ISNUMBER(--c)))) | 4471, 20931, 12… | Excel 365, easier to read: LET names the list of characters, and FILTER keeps the ones that convert to numbers. |
Common mistakes
- All digits are joined into one number: "2 boxes of 50" gives 250. This works when each cell holds one number.
- Decimal points are dropped, so "12.5 kg" becomes 125. In Google Sheets, extract the first number with its decimals instead:
=VALUE(REGEXEXTRACT(A2,"\d+(\.\d+)?")). - The double minus drops leading zeros ("0042" becomes 42). Leave it off to keep the digits as text.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Extract numbers from a text string
B2, extract the digits from the note and return them as a number, then fill down to B7.Questions
How do I keep the result as text with its leading zeros?
Drop the double minus: =CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,"")).
How do I remove the numbers and keep the text?
Keep the characters that aren't digits: =CONCAT(IF(ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1)),"",MID(A2,SEQUENCE(LEN(A2)),1))).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.