📎 This article includes 1 downloadable practice file ↓
Bank narrations, ERP exports and copied web data hide the numbers you need inside text: “NEFT-HDFC-REF 4471029-Raj Traders”, “Qty: 12 pcs”, “Rs 12,500 paid on 3rd”. Excel can pull them out — which method depends on your Excel version and how regular the text is.
In this article
When the position is predictable: LEFT, MID, TEXTAFTER
=RIGHT(A2, 4) ' "INV-2026-0045" → "0045"
=TEXTAFTER(A2, "-", -1) ' last part after the final dash → "0045"
=TEXTBEFORE(TEXTAFTER(A2, "Rs "), " ") ' "Paid Rs 12,500 via UPI" → "12,500"These return text. Wrap with -- or VALUE() to get a number: =--SUBSTITUTE(TEXTBEFORE(TEXTAFTER(A2,"Rs ")," "),",","") → 12500.
All the digits, wherever they are (Excel 365)
=TEXTJOIN("", TRUE, IFERROR(--MID(A2, SEQUENCE(LEN(A2)), 1), ""))SEQUENCE(LEN(A2))makes positions 1, 2, 3… up to the length.MID(A2, …, 1)splits the text into single characters.--turns digits into numbers and everything else into errors.IFERROR(…, "")drops the errors;TEXTJOINglues the digits back together.
“Call 98765 43210 after 6” becomes “98765432106” — notice it also grabbed the 6. That’s the limitation: it collects every digit.
A specific number pattern: REGEXEXTRACT (newest Excel 365)
=REGEXEXTRACT(A2, "[6-9]\d{9}") ' 10-digit Indian mobile
=REGEXEXTRACT(A2, "\d+(\.\d+)?") ' first number, with decimals
=REGEXEXTRACT(A2, "INV-\d{4}-\d+") ' invoice codeRegular expressions describe the shape of what you want, so stray digits elsewhere are ignored. If your Excel doesn’t have REGEXEXTRACT yet, use the methods above or Power Query.
No formulas at all: Flash Fill
Type the number you want next to the first two rows, then press Ctrl+E. Excel guesses the pattern for the rest. Great for one-off cleaning; it doesn’t update when data changes.
Where people go wrong
| Problem | Fix |
|---|---|
| Result looks like a number but SUM ignores it | It’s text — add -- or VALUE() |
| Leading zeros lost (“0045” → 45) | Keep it as text if it’s a code, or format with "0000" |
| Commas break VALUE (“12,500”) | SUBSTITUTE the commas away first |
| Long numbers turn into 9.8765E+09 | Phone numbers are identifiers — keep as text, don’t convert |
Practice
The combo practice workbook below has a Sales sheet of 200 orders and a task for every formula on this page. Type your formula in the yellow column; the check turns green when the answer matches. The Answers sheet has working versions.
More combinations: all formula combos · functions used here are explained in the Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.