# How to extract numbers from a text string in Excel

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","")).

**Excel:** `=--CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))` (Microsoft 365 only)

**Google Sheets:** `=VALUE(REGEXREPLACE(A2,"\D",""))`

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Note | Number |
| Order 4471 shipped |  |
| Ref: INV-20931 |  |
| Qty 12 boxes |  |
| Room B205 |  |
| Call ext. 3318 today |  |
| Code 0042-A |  |

Formula in B2, filled to B7: `=--CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))`

Result: 4471; 20931; 12; 205; 3318; 42

## 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.

## Other ways

- `=LET(c,MID(A2,SEQUENCE(LEN(A2)),1),--CONCAT(FILTER(c,ISNUMBER(--c))))`: 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.

## 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)))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/extract-numbers-from-text