Need to pull the digits out of labels like “Order #1024” or “Qty: 36 units”? One formula walks every character, keeps the numbers, drops everything else, and returns a real number you can sum or sort.
SEQUENCE visits every character position; digits survive, letters and symbols become blanks, and the final *1 turns the leftover text back into a number.
The example
A column of messy labels, each with one number buried in text. You want just the number.
| A | B | |
|---|---|---|
| 1 | Raw text | Number |
| 2 | Order #1024 (large) | 1024 |
| 3 | SKU-00857 | 857 |
| 4 | Qty: 36 units | 36 |
| 5 | Weight 12 kg | 12 |
The formula
The same formula handles each row — it reads the cell one character at a time:
How it works
Working from the inside out:
SEQUENCE(LEN(A2))lists the position of every character: 1, 2, 3, … to the end of the text.MID(A2, …, 1)pulls the single character sitting at each of those positions.- Multiplying each character by 1 turns digits into numbers; letters and symbols throw an error, which
IFERRORswaps for a blank. TEXTJOINglues the surviving digits back together, and the final*1converts that text into a real number.
Want to keep the digits as text (to preserve leading zeros like 00857)? Drop the final *1.
Try it: interactive demo
Type any messy text — the digits are extracted into a number live.
Variations
Works in any Excel (no SEQUENCE)
Older Excel has no SEQUENCE. Swap in ROW(INDIRECT(...)) and confirm with Ctrl+Shift+Enter so it runs as an array formula.
Grab just the first number
If a cell has several numbers and you only want the first run of digits, REGEXEXTRACT is cleanest in Excel 365.
Pitfalls & errors
If a cell holds more than one number (e.g. Room 12, Floor 3), this joins every digit into one value (123). Pull each number separately if that matters.
Turning the result into a number drops leading zeros — 00857 becomes 857. Remove the final *1 to keep it as text.
No digits at all makes the closing *1 return #VALUE!. Wrap the whole formula in IFERROR(…,"") to leave those cells blank.
Practice workbook
Frequently asked questions
How do I keep leading zeros?
What if the cell has two separate numbers?
Does this work in Excel 2019 or earlier?
How do I include a decimal point?
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