Extract Numbers From Messy Text

Excel & Google Sheets

How it works

In Google Sheets, REGEXEXTRACT returns the first unbroken run of digits it finds — the pattern [0-9]+ means "one or more digits in a row." What comes back is text, not a number, so multiply by 1 before comparing it to real numbers: =REGEXEXTRACT(A2,"[0-9]+")*1. Classic Excel has no regex functions, so the Excel version tests every character instead: SEQUENCE(LEN(A2)) counts positions 1 through the length of the text, MID grabs the character at each position, and multiplying by 1 keeps digits while turning everything else into an error. IFERROR swaps those errors for empty text, and TEXTJOIN glues the survivors back into one string — leaving only the digits. It looks intimidating, but it is a single copy-paste that handles any text you throw at it.

SEQUENCE(LEN(A2))

Builds the list 1, 2, 3, … up to the length of the text — one entry per character.

MID(A2,…,1)

Pulls out the single character at each of those positions.

*1

Digits survive as numbers; letters, spaces, and symbols become #VALUE! errors.

IFERROR(…,"")

Replaces every errored (non-digit) character with empty text.

TEXTJOIN("",TRUE,…)

Glues what's left back together into one string of digits.

When to use it

Use it when order numbers, invoice IDs, or quantities are trapped inside notes and subject lines, and you need the bare number to look up against another sheet or add up in a report.

Common mistakes

• ✕Treating the result as a number.

  ✓Both formulas return text — a "4521" that SUM skips and that won't match a real 4521 in a lookup. Append *1 to convert (note it drops leading zeros, so "0087" becomes 87).

• ✕Running the Excel formula in Excel 2019 or older.

  ✓SEQUENCE and dynamic arrays need Excel 365 or 2021 — older versions show #NAME?. Fall back to Flash Fill (Ctrl+E) or ask for the export with the number in its own column.

• ✕Text with more than one number group.

  ✓The two versions disagree: on "Ship 15 of 30", REGEXEXTRACT grabs only the first group (15), while the Excel digit filter merges everything into 1530. Check which number you actually want before trusting either.