Extract Numbers from Text

Excel Formulas › Text

Excel 365Legacy alt

Pulling the number out of a mixed string — 123 from INV-123, a quantity from a label — is a frequent cleanup job. When the number sits after a known marker it’s a one-liner; for digits scattered anywhere, a modern array formula does it.

Quick formula: to get the number after the dash in A2 (e.g. INV-123):
=VALUE(TEXTAFTER(A2, "-"))
TEXTAFTER grabs everything after the dash; VALUE turns that text into a real number you can calculate with.

Functions used (tap for the full reference guide):

TEXTAFTERMIDVALUEFIND

The example

Invoice codes with the number after a dash.

AB
1CodeNumber
2INV-123123
3INV-45704570
4INV-8888

The formula

The number, as a real value:

=VALUE(TEXTAFTER(A2, "-")) // "INV-123" → 123

How it works

Two functions, one clear job each:

  1. TEXTAFTER(A2, "-") returns everything to the right of the dash — the text "123".
  2. VALUE(…) converts that text into the number 123, so it can be summed or compared.
  3. No dash? Use the marker that fits — TEXTAFTER(A2, " ") for a space, or the legacy MID/FIND approach below.

Digits scattered anywhere (like A12B3)? In Excel 365: =VALUE(TEXTJOIN("",1,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))) — it tests each character and keeps only the digits.

Try it: interactive demo

Live demo

Type a code; see the number pulled out (after the dash, or all digits).

After dash:   All digits:

Variations

Legacy: number after a marker

Any version, when digits follow a known character:

=VALUE(MID(A2, FIND("-", A2) + 1, 10))

Number before a marker

TEXTBEFORE grabs the left side:

=VALUE(TEXTBEFORE(A2, " "))

All digits anywhere (365)

Keep only numeric characters:

=VALUE(TEXTJOIN("",1,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,"")))

Pitfalls & errors

#VALUE! from VALUE. The extracted text wasn’t purely numeric (an extra letter or space slipped in). Trim it, or wrap in IFERROR.

Leading zeros vanish. VALUE turns "007" into 7. If the code must stay text (an ID), skip VALUE and keep the string.

TEXTAFTER/TEXTSPLIT need Excel 365. Older versions show #NAME? — use the MID/FIND legacy formula.

Practice workbook

📊
Download the free Extract Numbers from Text practice workbook
Invoice codes with the live MID/FIND number extraction, the TEXTAFTER and all-digits variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I extract a number from text in Excel?
If the number follows a marker, use =VALUE(TEXTAFTER(A2, "-")) in Excel 365, or =VALUE(MID(A2, FIND("-", A2)+1, 10)) in older versions. VALUE turns the extracted text into a real number.
How do I extract digits scattered anywhere in a string?
In Excel 365 use =VALUE(TEXTJOIN("",1,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))), which keeps only the numeric characters.
Why does my extracted number lose leading zeros?
VALUE converts text to a number, dropping leading zeros (007 becomes 7). If the value is an identifier, keep it as text and don't apply VALUE.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Split text into columns · Clean up messy text · Extract text between characters

Function references: TEXTAFTER · MID · VALUE · FIND