Yes, Excel can extract numbers from a cell that contains text, and the fastest way depends on your version. In Excel 365, use =TEXTJOIN("",TRUE,IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1,"")) as an array formula (entered with Ctrl+Shift+Enter in older versions) to return only the digits from cell A1. For a one-time task, you can also use Flash Fill (Ctrl+E) or the newer TEXTSPLIT and VSTACK functions in Excel 365 to isolate numbers without writing a complex formula.
What Does “Left Numbers Only” Mean?
Users often search for this when they have a column like “Order #12345” or “Item-9876” and want to keep just the numeric part. The phrase “left numbers only” usually means extracting digits from a mixed string, not the LEFT function itself. The LEFT function returns characters from the left side of a cell, but it cannot filter out non-numeric characters. To get numbers only, you need a formula that checks each character and keeps only digits.
Method 1: The Classic Array Formula (Works in All Versions)
This formula works in Excel 2010 through 365, but in versions before Excel 365 you must enter it as an array formula by pressing Ctrl+Shift+Enter (on Mac: Cmd+Shift+Enter).
Assume your text is in A2. In B2, enter:
=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1,""))
How it works:
ROW(INDIRECT("1:"&LEN(A2)))creates an array of numbers from 1 to the length of the text (e.g., 1,2,3…).MID(A2,ROW(...),1)extracts each single character.- Multiplying each character by 1 converts numeric characters to numbers and forces text characters to produce a #VALUE! error.
IFERROR(...,"")replaces errors with an empty string.TEXTJOIN("",TRUE,...)concatenates the remaining numeric characters with no separator.
For example, if A2 contains “SKU-4521”, the formula returns “4521”. If the cell contains no digits, it returns an empty string.
Method 2: Flash Fill (Easiest for One-Time Tasks)
Flash Fill (introduced in Excel 2013) can extract numbers without any formula. Type the expected result in the adjacent cell, then press Ctrl+E (Windows) or Cmd+E (Mac). For instance, if A2 is “Item 123”, type “123” in B2, select B2, and press Ctrl+E. Excel will fill the rest of the column with the numbers extracted from each cell. Flash Fill works best when the pattern is consistent, and it is not dynamic—if you change the source data, the extracted values do not update automatically.
Method 3: Using TEXTSPLIT and VSTACK (Excel 365 Only)
If you have Excel 365 or Excel 2024, you can use a more modern approach. To extract numbers from a single cell, you can combine TEXTSPLIT with array operations, but the simplest is to use LET and MID with SEQUENCE:
=LET(s,A2, TEXTJOIN("",TRUE,IFERROR(MID(s,SEQUENCE(LEN(s)),1)*1,"")))
This does not require Ctrl+Shift+Enter because SEQUENCE creates the array dynamically. For multiple cells, you can use VSTACK to combine results, but it is often easier to drag the formula down.
How to Choose the Right Method
Consider these factors:
- Excel version: Flash Fill works in 2013 and later; the array formula works in all versions; TEXTSPLIT and VSTACK require 365 or 2024.
- Need for automation: If the source data changes, use a formula (Method 1 or 3). Flash Fill is static.
- Complexity: For a quick, one-off cleanup, Flash Fill is fastest. For a reusable formula, use the array formula.
- Numbers with decimals or signs: The formulas above only keep digits (0-9). They will remove minus signs, decimal points, and currency symbols. If you need to keep a minus or decimal, modify the formula to include those characters.
Common Problems and Solutions
Here are typical issues users encounter:
- Formula returns #VALUE! error: This happens if you use the array formula in a version that requires Ctrl+Shift+Enter but you pressed Enter alone. Re-enter with Ctrl+Shift+Enter.
- Leading zeros disappear: If the extracted number has leading zeros (e.g., “007”), the formula returns “7” because the result is a number. To keep leading zeros, format the cell as Text before entering the formula, or use a formula that returns text (the TEXTJOIN formula returns text, so it should preserve leading zeros—check your version).
- Numbers with decimals: The formula strips the decimal point. To keep decimals, use a more complex formula that checks for “.” and “-“. For example, to keep digits, minus, and decimal point, use:
=TEXTJOIN("",TRUE,IF(ISNUMBER(SEARCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"0123456789.-")),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))entered with Ctrl+Shift+Enter. - Flash Fill not triggering: Make sure the pattern is clear. If Excel doesn’t suggest the fill, type the first two results manually, then select both and press Ctrl+E.
Alternative Approaches and Comparisons
Besides the methods above, you can use:
- Power Query (Get & Transform): In Excel 2016 and later, you can load your data into Power Query, add a custom column with a formula like
=Text.Select([Column1], {"0".."9"}), and load back. This is useful for large datasets and is repeatable. - VBA macro: For a permanent solution, you can write a macro that loops through cells and extracts digits. This is overkill for most users, but if you need to process many sheets, it can be efficient. Remember to save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z.
- Find and Replace with wildcards: If the numbers are at the end of the text and you want to remove the text, you can use Find and Replace with wildcards, but it is limited and not recommended for complex cases.
Compared to the array formula, Power Query is more visual and easier for non-formula users, but it requires a few more steps. Flash Fill is the simplest but not dynamic. The array formula is the most portable across versions.
Frequently Asked Questions
How do I extract numbers from a cell in Excel without using a formula?
Use Flash Fill. Type the expected result in a new column, then press Ctrl+E (Windows) or Cmd+E (Mac). Excel will detect the pattern and fill the rest. This works best when the numbers are consistently positioned.
Why does my formula return a 0 instead of the number?
If your cell contains text like “ABC123”, the formula should return 123. A 0 might appear if the cell contains no digits, or if you accidentally multiplied by 1 and the result is an empty string that Excel treats as 0. Check that the cell is formatted as General or Text, and ensure the formula is entered correctly.
Can I extract numbers from multiple cells at once?
Yes. Drag the formula down, or use Flash Fill for a range. In Excel 365, you can also use an array formula that spills results, for example =TEXTJOIN("",TRUE,IFERROR(MID(A2:A10,TRANSPOSE(ROW(INDIRECT("1:"&MAX(LEN(A2:A10))))),1)*1,"")) entered with Ctrl+Shift+Enter, but it is easier to just fill down.
How do I keep leading zeros when extracting numbers?
The TEXTJOIN formula returns text, so leading zeros are preserved. If you use a different method that returns a number, format the cell as Text before entering the formula, or use the TEXT function to convert the result to text, e.g., =TEXT(your_formula,"0").
Does this work in Google Sheets?
Yes, but the syntax differs. In Google Sheets, you can use =REGEXEXTRACT(A1,"\d+") to get the first run of digits, or =JOIN("",REGEXEXTRACT(A1,REPT("(\d)",LEN(A1)))) to get all digits. The TEXTJOIN array formula also works in Google Sheets with the same logic.
What if the numbers are separated by text, like “12abc34”?
The formulas above will return “1234” because they extract all digits and concatenate them. If you need to keep them separate, you would need a different approach, such as using TEXTSPLIT or a VBA macro.
Conclusion
Extracting numbers from mixed text in Excel is a common task with several reliable solutions. For most users, the array formula with TEXTJOIN is the most versatile, while Flash Fill is the quickest for one-off jobs. Choose based on your Excel version and whether you need a dynamic result. Always test on a copy of your data first, especially when using macros or complex formulas.
Related
- How to Unmerge Cells in Excel (Group of Cells)
- Excel HR Templates for Attendance: A Practical Guide
- Excel How to Ungroup Cells: Rows, Columns & Sheets
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Current Date: TODAY ExplainedIn Microsoft Excel for Microsoft 365 on Windows, enter =TODAY() in a cell to display the current date.…
- How to Filter Excel by Date: Ranges, Months, and YearsTo filter Excel by date, select a cell in your data, choose Data > Filter, open the arrow…
- How to Sort and Filter a Table in Excel Step by StepTo sort and filter an Excel table, select a cell in it and use the arrow in a…
- Split Text in Excel Without a Formula: Easy MethodsYes. In Excel for Microsoft 365 on Windows, split text without a formula using Data > Text to…
- Link String to Function in Python: How-ToYes, you can link a string to a function in Python, and the most direct way is to…
- Sort in Excel Smallest to Largest: Step-by-Step GuideTo sort values in Excel from smallest to largest, click a cell in the number column, then select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.