Text formula page
Extract Numbers From Text Formula
Use this when imported IDs or labels contain a numeric part you need to isolate.
Extract numbers from text in Excel or Google Sheets.
If A2 is INV-1001-East, the result is 1001.
Copy formulas
=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1)*1, ""))=REGEXREPLACE(A2, "\D", "")Google Sheets removes every non-digit with REGEXREPLACE. Modern Excel scans each character with SEQUENCE, converts digits with *1, and joins the remaining characters.
Example data
| Raw Text | Example Result |
|---|---|
| Maya Chen | Maya |
| SKU-1001-East | SKU |
| https://www.example.com/pricing | example.com |
| Acme North | Acme North |
If A2 is INV-1001-East, the result is 1001.
How the formula works
- The Excel formula checks each character.
- Digits are kept and non-digits become blanks.
- The Google Sheets formula removes every non-digit character.
| Syntax piece | Role in the formula |
|---|---|
| SEQUENCE(LEN(A2)) | The Excel positions for every character in A2. |
| MID(A2, ..., 1) | The Excel character-by-character extraction. |
| *1 | The Excel numeric test that keeps digits and errors on non-digits. |
| TEXTJOIN | The Excel function that combines kept digits without separators. |
| \D | The Google Sheets regex matching every non-digit character. |
Verified examples
=TEXTJOIN("", TRUE, IFERROR(MID(A2, SEQUENCE(LEN(A2)), 1)*1, ""))Excel: Enter INV-1001-East in A2. Returns: 1001
=REGEXREPLACE(A2, "\D", "")Google Sheets: Enter INV-1001-East in A2. Returns: 1001
=REGEXREPLACE(A2, "\D", "")Google Sheets: Enter Item 12.50 in A2. Returns: 1250; the decimal point is removed, not preserved.
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| A decimal or negative number loses its punctuation | The formulas keep digits only, so decimal points, minus signs, and commas are removed. | Use a number-parsing pattern that explicitly preserves the punctuation required by the data. |
| Text with no digits returns blank | Every character is removed or converted to an empty string. | Wrap the formula in a fallback if no-digit input should show a message. |
| Excel cannot evaluate SEQUENCE | The workbook does not support the dynamic-array functions used by the formula. | Use a modern Excel version or the Google Sheets REGEXREPLACE approach. |
When not to use this formula
- Do not use this pattern when decimal precision, signs, or currency formatting must be preserved.
Alternatives
| Alternative | When to use it |
|---|---|
| REGEXEXTRACT Formula Builder | Use when the required numeric pattern is more specific than all digits. |
| Split Text Formula | Use when the text should be divided into tokens instead of reduced to digits. |
Related formulas
Official references
- TEXTJOIN function from Microsoft
- SEQUENCE function from Microsoft
- REGEXREPLACE function from Google
FAQ
Is the output a parsed decimal number?
No. It is a digit string, and punctuation such as a decimal point is removed.
What happens when the source has no digits?
The formulas return an empty result because no characters qualify as digits.