To filter Excel rows where a text cell contains one or more digits, add a helper column with =SUMPRODUCT(--ISNUMBER(SEARCH({0,1,2,3,4,5,6,7,8,9},A2)))>0, fill it down, then use Data > Filter and select TRUE in that helper column. Excel’s ordinary Number Filters work on numeric cell values, not on digits embedded in labels such as “Order 48-B,” so the helper-column method is usually the reliable answer.
What “numbers from text” can mean
The phrase can describe several different spreadsheet tasks. You might want to show rows where a text label contains any digit, identify cells that look numeric but are stored as text, extract the digits from a label, or filter rows based on a number included in a longer description. These are related problems, but they need different tests.
For example, consider a column containing “Desk 12,” “No code,” “Unit 7A,” and “204.” The first three entries are text that include digits (except “No code”), while “204” may be an actual number or a text string that looks like one. A standard filter can treat those values differently depending on their underlying cell type.
- Contains any digit: matches text such as “Unit 7A” or “Desk 12.”
- Is a number: matches numeric values such as 204, but not necessarily text that looks numeric.
- Is numeric text: matches a text entry such as “204,” but not “Unit 204A.”
- Extract digits: produces “12” from “Desk 12,” which you can then use or filter separately.
Decide which result you need before filtering. A test for any digit does not mean that a cell contains only a number, and extracting digits does not automatically interpret commas, decimal points, signs, or units correctly.
Filter rows when text contains any digit
Assume the labels are in A2:A100, with a header in A1. Enter the following formula in B2, then copy it down through B100:
=SUMPRODUCT(--ISNUMBER(SEARCH({0,1,2,3,4,5,6,7,8,9},A2)))>0
The formula searches A2 for each digit from 0 through 9. SEARCH returns a position when it finds a digit and an error when it does not; ISNUMBER turns those results into TRUE or FALSE. SUMPRODUCT counts the successful matches, and >0 returns TRUE if at least one digit appears. For “Unit 7A,” the result is TRUE; for “No code,” it is FALSE. The formula also returns TRUE for a cell containing only “204.”
Once the helper results are filled in, select a cell in the data range and choose Data > Filter. Open the dropdown in column B and select TRUE. Excel will display the rows whose labels contain digits. Keep the helper column in the sheet if you expect to repeat the task: it makes the rule visible and easy to adjust.
The formula checks for digits anywhere in the cell, not for a complete numeric value. It treats “A1B2” as a match, and it does not tell you whether the digits represent an order number, a date, or a quantity. It is also case-insensitive, which is immaterial for digits. In regional Excel settings, list separators may differ; if Excel rejects the formula, check whether your installation expects semicolons instead of commas.
Filter numeric values and numbers stored as text
If a column should contain only numbers, start with the built-in filter. Select a cell in the table or data range and choose Data > Filter. Open the column dropdown, point to Number Filters, and choose a condition such as Equals, Greater Than, or Between. The exact choices depend on the filter condition you select. This approach is suitable for cells Excel recognizes as numeric values.
A value that looks like a number may actually be text. Common causes include importing a CSV file, a leading apostrophe, a space before or after the digits, or inconsistent source-system formatting. Such a cell may not behave as expected under Number Filters or in arithmetic. A green error indicator may appear, but its absence does not prove every value has the intended type.
To check whether a cell contains a value that Excel can convert to a number, use a helper formula. With the candidate value in A2, enter this in B2 and fill down:
=IFERROR(ISNUMBER(VALUE(A2)),FALSE)
For the text “204,” this returns TRUE; for “Unit 204A,” it returns FALSE. VALUE converts recognized numeric text to a number, and IFERROR supplies FALSE when conversion fails. This is a conversion test, not a universal data-quality rule: strings that Excel interprets as dates, times, or currency can also be recognized as numeric values. If a column must contain a particular kind of number, add checks for the required range or format rather than relying on conversion alone.
When the values are meant to be numbers, you can convert a selected range using the warning menu, Text to Columns, or a deliberate formula-based cleanup, depending on the source and data. Do not convert identifiers automatically: “00127” may be an ID whose leading zeros matter, not a quantity that should become 127.
Extract digits from a text cell
If you need the digits themselves rather than a TRUE/FALSE flag, Excel for Microsoft 365 or Excel 2021 can assemble digit characters from a string. With the source text in A2, enter this formula in B2:
=TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))
For “Order 48-B,” the result is the text “48.” LEN counts the characters, SEQUENCE generates their positions, and MID takes one character at each position. Multiplication by 1 converts digit characters to numeric characters; IFERROR replaces non-digits with empty text; TEXTJOIN combines what remains. The result is text, which is often useful for matching or display but may need conversion before arithmetic.
This formula simply removes every non-digit character. It would turn “12.50” into “1250,” and “-8” into “8.” It is therefore appropriate for extracting an identifier made only of digits, but not for interpreting decimal or negative numbers. If the text format is predictable, extracting a value after a known label or delimiter is safer than stripping characters indiscriminately. For an empty source cell, handle blanks separately if you need to avoid a calculation error.
Filter the matching rows with a formula
A helper column is easiest when you want to use Excel’s dropdown filters. If you instead want a separate, automatically updating list, the FILTER function can return matching rows. Assume the records occupy A2:C100, and the TRUE/FALSE digit test described above has been filled down in D2:D100. In an empty area of the worksheet, enter:
=FILTER(A2:C100,D2:D100=TRUE,"No matching rows")
The formula returns the rows from A:C whose corresponding helper value in D is TRUE, or the stated message if there are no matches. FILTER is available in Microsoft 365 and Excel 2021 or later. Leave enough empty space below and beside the formula for the results to spill; existing content in the spill area can produce a #SPILL! error. If the source list grows, adjust the ranges or use an Excel Table so the data can expand more conveniently.
For a simple manual review, you may not need a formula-generated copy at all. The filter dropdown hides nonmatching rows without deleting them, while FILTER creates a separate result array. Choose the dropdown when you need to inspect or edit the original records, and a formula result when you want a live view elsewhere in the workbook.
Use text filters, tables, and imported data carefully
Excel’s built-in Text Filters menu includes conditions such as Contains and Begins With. It can find a particular digit—for example, entries containing “7”—but it does not provide a single straightforward “contains any digit from 0 to 9” condition. You could use a text filter for one known digit, but checking all digits manually is cumbersome and easy to get wrong. The helper formula scales better when the criterion is any digit.
For a structured list, convert the range to a table with Insert > Table (or use Home > Format as Table). Tables provide filter dropdowns and expand as records are added. A helper formula entered in a table column generally fills down for the table’s rows, making the test easier to maintain. Check that new imported records are actually included in the table before relying on the results.
Imported files often mix numeric values and text. A column may contain “25,” “25A,” and “ 25 ”, or different conventions such as “1,250” and “1.250.” Before converting or filtering, inspect a sample and decide what the characters mean in the source system. Excel’s interpretation of decimal and thousands separators can depend on regional settings. Preserve a copy of the original column when cleaning important data, so you can compare the transformed values and recover identifiers or formatting that should not have been changed.
Common problems and practical checks
- The formula says FALSE for a value that looks numeric: confirm the formula refers to the intended cell and that the text contains ordinary digits. Some imported characters resemble digits but are different characters.
- Only some rows appear: check that the helper formula was filled down for every record and that the filter range includes all rows. A blank row can also split a range when applying filters.
- Number Filters do not find a value: the cell may be text. Use the VALUE test to diagnose convertibility, then decide whether conversion is appropriate for the data.
- Results change after sorting: sort the complete table or data range, including the helper column, so each result remains beside its source record.
- The extraction formula gives an unexpected number: it removes punctuation and letters. Review signs, decimal separators, and leading zeros before treating the output as a quantity.
In Microsoft 365 for Windows, the ribbon paths above apply. Excel for Mac has the same core Data > Filter workflow, though ribbon placement and some dropdown presentation can vary by release. Excel for the web also supports filtering and modern formulas, but its interface may differ slightly. If a formula is unavailable in an older desktop release, use the helper test with standard functions or filter the data in a compatible newer version.
Frequently Asked Questions
Can Excel filter cells that contain a number inside text?
Yes. Add a helper column that tests for digits, then filter that column for TRUE. Standard Number Filters target numeric values, so they are not a dependable way to find digits embedded in labels such as “Room 12.”
How do I filter text that contains a specific digit?
Use the column dropdown, choose Text Filters > Contains, and enter the digit, such as 7. This finds cells containing that character; it does not test whether a cell contains any digit at all.
How can I tell whether a number is stored as text?
For a candidate in A2, =IFERROR(ISNUMBER(VALUE(A2)),FALSE) returns TRUE when Excel can convert it to a numeric value. Remember that dates and other numeric formats may also pass, so confirm that the value has the meaning your column requires.
Can I extract only the digits from a label?
Yes. In Microsoft 365 or Excel 2021, the TEXTJOIN, MID, and SEQUENCE formula above extracts digit characters. It discards punctuation and signs, so it is not a safe way to parse decimal or negative measurements without additional rules.
Why does Excel treat a number in a cell as text?
Imported data, leading apostrophes, extra spaces, and inconsistent formatting are common causes. Check the source and intended meaning before converting: a code with leading zeros may need to remain text even though it consists only of digits.
Related
- Excel Data Validation List Without a Cell Reference?
- Excel Data Validation List: Can You Select Multiple?
- Excel Data Validation List: Remove Duplicates
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Average Days Between Dates in Excel: Formulas & TipsIf your dates are listed in chronological order in A2:A10, enter =AVERAGE(A3:A10-A2:A9) to calculate the average number of…
- XLOOKUP Formula: Real Examples in ExcelThe basic XLOOKUP formula is =XLOOKUP(E2,A2:A6,B2:B6): it looks for the value in E2 within A2:A6 and returns the…
- Clear a Filter in an Excel Table: Steps and OptionsTo clear a filter in an Excel table, open the drop-down arrow in the filtered column’s header and…
- XLOOKUP Showing Formula Instead of Result?If XLOOKUP displays the formula itself instead of a result, first turn off Show Formulas on the Formulas…
- COUNTIF Excel Not Equal: Formulas, Blanks, and ExamplesTo count cells that are not equal to a value in Excel, use =COUNTIF(A2:A20,"<>Apple"). The <> operator means…
- HLOOKUP Examples in Excel: Formulas and Common FixesIn Excel, HLOOKUP searches across the first row of a range and returns a value from a specified…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.