Yes. In Excel for Microsoft 365, you can pull every digit from a text cell with a formula, then return the result as a number if that is what you need. For example, text such as “Ref AB-2047 / box 3” can produce 20473. That method removes punctuation and letters too, so it is not suitable when decimal points, minus signs, or separate numbers must keep their meaning.
What “pull numbers” can mean
Before choosing a method, decide what counts as the number you want. A text string may contain an order ID, a quantity, a date, a price, or several unrelated numbers. “Extract all digits” is different from “return the first number,” and both differ from converting a formatted amount into a usable numeric value.
For example, extracting digits from “Invoice 12-500” gives 12500, while a reader might intend two separate values, 12 and 500. Extracting digits from “$1,234.50” gives 123450, not 1234.50. The formula below is useful when the goal really is to collect every digit in the cell, such as removing letters from a product code or gathering digits from a mixed reference.
Think about the result you need before filling a formula down a long list. If the numbers are identifiers, preserving their exact characters may matter more than making them arithmetic-ready. If they are measurements or prices, punctuation and signs are part of the value and should not be discarded.
Extract every digit with a formula
With the mixed text in A2 and the output formula in B2, enter this formula. It scans each character, keeps characters that can be converted to digits, and joins them in their original order:
=IF(A2="","",LET(x,A2,d,TEXTJOIN("",TRUE,IFERROR(MID(x,SEQUENCE(LEN(x)),1)*1,"")),IF(d="","",VALUE(d))))
For A2 containing “Ref AB-2047 / box 3,” the result in B2 is the number 20473. The formula returns a blank if A2 is blank or contains no digits. Copy or fill the formula down to process the rest of the column. LET and SEQUENCE are available in Microsoft 365 and Excel 2021 or later; an older Excel version may not recognize them.
TEXTJOIN combines the kept characters, while MID examines one character at a time. Multiplying each character by 1 makes a digit convertible to a number; IFERROR substitutes an empty string when the character is a letter, space, or punctuation mark. VALUE converts the joined digits to a numeric result. Because the final conversion makes a number, Excel can use it in arithmetic, but it can also remove leading zeros.
If you want the extracted digits to remain text, so that “0042” stays “0042,” use this version instead. With the source text in A2 and the result in B2, it returns the joined digits as text:
=IF(A2="","",LET(x,A2,TEXTJOIN("",TRUE,IFERROR(MID(x,SEQUENCE(LEN(x)),1)*1,""))))
For “Batch 0042-X,” that formula returns the text 0042. The two formulas answer different needs: use the numeric result for calculations, and the text result for codes, account numbers, or labels where exact digits and leading zeros must be preserved.
Choose text or a numeric result
Excel distinguishes numbers from text that looks like a number. A numeric result can be added, sorted numerically, compared with numeric thresholds, or used in other calculations. Text digits are better for identifiers because Excel does not treat the leading zero as disposable formatting.
- Use a number for a quantity such as 20473, when arithmetic or numeric comparisons are expected.
- Keep text for a code such as 0042, a postal code, or an identifier whose digits should not be changed.
- Check long IDs carefully. Excel preserves only 15 significant digits in numeric values. A long identifier converted to a number may lose precision, so keep it as text.
Changing a cell’s number format to Text after Excel has already converted a long identifier does not restore digits that were lost. For imported IDs, set or preserve the column as text before conversion where possible, and inspect representative rows after importing.
Keep decimals, commas, and negative signs
The digit-only formula deliberately removes every non-digit character. That means it ignores decimal marks, grouping separators, currency symbols, spaces, and minus signs. “-8.25” becomes 825, and “1,234.50” becomes 123450. Do not use it for prices, measurements, percentages, or signed values unless that transformation is actually intended.
If each cell contains a consistently formatted amount such as “$1,234.50,” remove only the known currency and grouping characters, then convert the remaining text. With that exact type of amount in A2 and the formula in B2, this returns the numeric value 1234.5:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",",""))
This example assumes a dollar sign and comma thousands separators, with a period decimal mark. It is not a general-purpose parser for arbitrary prose: extra words, different currency symbols, or different regional separators may require different cleanup. Excel’s interpretation of decimal and thousands separators can depend on regional settings, so verify the result on sample rows before relying on it.
For inconsistent text such as “paid 1,234.50 USD” mixed with “refund -8.25,” first identify the exact number token and its formatting rules. A formula that simply strips characters cannot safely decide which punctuation is a decimal mark, which minus sign belongs to a value, or whether two digit groups are separate numbers.
Use Flash Fill for a quick one-time extraction
Flash Fill can infer a pattern from examples and fill a neighboring column. It is often convenient when the source list is small or the desired result is easy to demonstrate, but it produces filled values rather than a formula that recalculates from the source.
- Put the source text in column A and type the desired result beside the first row in column B.
- Type one or two more examples if the pattern could be interpreted in different ways. For instance, show clearly whether you want all digits or only the digits before a hyphen.
- On Windows, select the next cell in the output column and press Ctrl+E, or choose Data > Flash Fill. On Mac, use Data > Flash Fill if available in your Excel version; the keyboard shortcut can differ.
- Review the generated values, especially rows with unusual punctuation or multiple numbers.
Flash Fill is not a substitute for checking the rule. If one row says “Lot 4, shelf 12,” it may be unclear whether the intended output is 412, 4, or 12. Add a clear example, correct any mistaken results, and use a formula or repeatable query when new rows will arrive regularly.
Use Power Query for repeatable cleanup
Power Query is useful for larger lists and recurring imports because it records transformation steps that can be refreshed. In Excel for Microsoft 365 on Windows, select a cell in the source table and choose Data > From Table/Range. If the source is not already a table, Excel will prompt you to create one. In the Power Query Editor, choose Add Column > Custom Column.
For a column named Description, this Power Query M expression keeps only the digits and returns them as text:
Text.Select([Description], {"0".."9"})
Give the new column a descriptive name, confirm the expression, and inspect the preview before loading the result with Home > Close & Load. Keeping the output as text protects leading zeros and avoids numeric precision limits for long identifiers. If the values are genuinely quantities, change the new column’s data type only after confirming that the digit-only result represents the intended number.
Power Query steps can be refreshed after the source table changes, but they do not update merely because a source cell is edited until the query is refreshed. The commands and availability can vary by Excel platform and build; Excel for the web may have a different Power Query experience from desktop Excel. If the described editor options are not present, use a formula or check the capabilities of your installed version.
How to choose the right approach
Use the method that matches how often the data changes and how precise the extraction rule is. A formula is transparent and recalculates when its source cell changes. Flash Fill is quick for a small, stable job, but its output is a pattern-based result to review. Power Query is well suited to repeatable imports and documented cleanup steps.
- One small batch: Try Flash Fill, then review every unusual result.
- A live worksheet: Use a formula so the result updates with the source.
- Recurring files or many rows: Use Power Query and refresh the recorded steps.
- Amounts with decimals or signs: Parse a known format rather than removing all non-digits.
- Identifiers or long codes: Preserve the output as text to retain leading zeros and exact digits.
There is no single best choice for every “number in text” problem. A clean formula that returns the wrong interpretation is worse than a slower method that reflects the data’s meaning.
Common problems and checks
The formula shows #NAME? Check that your Excel version supports LET and SEQUENCE. Those functions require Microsoft 365 or Excel 2021 or later. Also confirm that the formula was entered with standard commas between arguments; some regional Excel settings use different list separators.
The result has no decimal or minus sign. That is expected for the digit-only formula. It intentionally keeps digits alone. Use a format-specific cleanup method if punctuation carries meaning.
The result has unexpected digits joined together. The formula combines every digit it finds. “Room 8, table 14” becomes 814. If numbers must remain separate, define which number to return or split the text using a reliable delimiter before extracting.
Leading zeros disappear. Use the text-returning formula, or keep the Power Query result as text. A numeric value cannot distinguish 0042 from 42; formatting can display zeros in some cases, but the underlying identifier is still numeric.
The value looks numeric but will not calculate. It may be stored as text. The numeric version of the formula uses VALUE to return a number, while the text version intentionally does not. Check a sample cell’s behavior in a calculation rather than relying only on how it looks.
Frequently Asked Questions
Can Excel extract only the digits from a text cell?
Yes. In Microsoft 365 or Excel 2021 and later, the formula above scans the cell and joins its digits. It returns all digits in order, not just the first number.
How do I extract the first number instead of every digit?
First define what counts as a number: for example, a run of digits, a decimal amount, or a signed value. The all-digits formula does not identify number boundaries. For a one-off, Flash Fill may work when examples are unambiguous; for recurring data, use a specifically designed formula or Power Query transformation and test it against edge cases.
Can I pull a number from text without losing decimals?
Yes, if the text follows a known format. Remove only its known currency or grouping characters and convert the remaining value, as in the $1,234.50 example. The digit-only method removes decimal punctuation and therefore changes the value.
Why does Excel remove zeros from the beginning?
Excel removes leading zeros when it stores the result as a number because 0042 and 42 have the same numeric value. Return text instead when the zeros are part of an ID, code, or other label.
Will the formula work in older Excel versions?
The formula shown uses LET and SEQUENCE, which are available in Microsoft 365 and Excel 2021 or later. In older versions, consider Flash Fill if available, Power Query if included in your installation, or a carefully reviewed alternative suited to the specific text pattern.
Related
- Password Protect an Excel Document From Opening
- Password Protect an Excel Document to Open
- Password Protect an Excel File Before Opening It Safely
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Calculate ROI Percentage in Excel With a FormulaTo calculate return on investment (ROI) in Excel, subtract the investment cost from its final value, divide the…
- Round Down in Excel: Formulas for Specific Decimal PlacesTo round a value down to a chosen number of decimal places in Excel, use the ROUNDDOWN function.…
- Excel Filter Drop-Down Shortcut: Open It QuicklyIn Excel for Microsoft 365 on Windows, select a cell in the filtered column’s header row and press…
- Inventory Tracking Spreadsheet: Setup, Formulas, and TipsAn inventory tracking spreadsheet is a worksheet that records what items you have, where they are, and when…
- Excel NPV Formula for Monthly Cash Flows, ExplainedFor equally spaced monthly cash flows in Excel, use the NPV function with a monthly discount rate, then…
- SUMPRODUCT in Excel: How Its Array Calculations WorkIn Excel, SUMPRODUCT multiplies corresponding values in one or more arrays and adds the resulting products. It is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.