Sometimes you need to find numbers in a range of Excel cells, but they are mixed with text: order numbers in comments, years in titles, house numbers in addresses. That makes the task harder than it seems.
First, let’s clarify the terminology. To “find” here means to determine whether a cell contains a certain character or not. The result is a Boolean value, TRUE or FALSE, which you can then filter by.
If by “find” you mean something else, for example to take the numbers out of the text or get rid of them, see these articles:
Digits are a group of 10 characters, 0 to 9. To check whether a cell contains any digit, you technically need to check each of them separately. One straightforward example of such a check is in the description of the IF function.
But can you replace ten separate checks with a single formula? Excel has no built-in function for that, but a combination of functions in one array formula does the job.
Also note that the ISNUMBER function won’t help here: it returns TRUE only if the whole cell is a number. For “Order 4512” it returns FALSE, although there are digits in the cell.
Formula to check for digits in a cell
This formula returns TRUE if the cell contains at least one digit, and FALSE otherwise:
=COUNT(SEARCH({1,2,3,4,5,6,7,8,9,0},A1))>0
How it works: the SEARCH function looks for each of the 10 digits in the cell and returns either its position or an error. COUNT counts only the numbers, that is, the digits found. If there is at least one, the result is greater than zero. You don’t need Ctrl+Shift+Enter here, because the array is written right in the formula.
The same logic is used to detect uppercase letters or to find letters and numbers in a cell.
In Excel 365, the built-in REGEXTEST function solves the same task with a regular expression, where \d means any digit:
=REGEXTEST(A1,"\d")
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | ISNUMBER | COUNT(SEARCH) | REGEXTEST (365) |
| 2 | Order 4512 | FALSE=ISNUMBER(A2) | TRUE=COUNT(SEARCH({1,2,3,4,5,6,7,8,9,0},A2))>0 | TRUE=REGEXTEST(A2,"\d") |
| 3 | no digits | FALSE=ISNUMBER(A3) | FALSE=COUNT(SEARCH({1,2,3,4,5,6,7,8,9,0},A3))>0 | FALSE=REGEXTEST(A3,"\d") |
| 4 | Room B | FALSE=ISNUMBER(A4) | FALSE=COUNT(SEARCH({1,2,3,4,5,6,7,8,9,0},A4))>0 | FALSE=REGEXTEST(A4,"\d") |
| 5 | 2026 | TRUE=ISNUMBER(A5) | TRUE=COUNT(SEARCH({1,2,3,4,5,6,7,8,9,0},A5))>0 | TRUE=REGEXTEST(A5,"\d") |
| 6 | call 911 | FALSE=ISNUMBER(A6) | TRUE=COUNT(SEARCH({1,2,3,4,5,6,7,8,9,0},A6))>0 | TRUE=REGEXTEST(A6,"\d") |
ISNUMBER is TRUE only when the whole cell is a number (2026), so it misses “Order 4512”. COUNT(SEARCH(…)) and REGEXTEST check for any digit inside the text.
Here is how the formula works on real data:
| Cell | Result |
|---|---|
| Order 4512 shipped | TRUE |
| Call me later | FALSE |
| 2001: A Space Odyssey | TRUE |
| N/A | FALSE |
If you need not just to check, but to count the digits in a cell, see how to count characters in Excel.
“Find digits” procedure in !SEMTools
The formula works, but it has to be remembered, typed in, filled down and then replaced with values. The easiest way is the built-in procedure that becomes available after you install !SEMTools. Copy the source column, select the copy and click the procedure: each cell turns into TRUE or FALSE right in place.
How to find only certain digits in Excel
If you need specific digits, for example only even or only odd ones, there are two approaches: a regular expression or a modified version of the formula above.
Find specific digits using regex in !SEMTools
With !SEMTools, you can describe the digits you’re looking for with a regular expression. Just put the digits in square brackets and click OK. For even digits, the pattern is [02468]:
Find specific digits using a formula
In the array formula, leave only the digits you need. For even digits:
=COUNT(SEARCH({2,4,6,8,0},A1))>0
The same technique works for sequences of digits of any length. Note the quotes: without them, Excel turns 01 into 1, and the formula would look for “1” instead of “01”:
=COUNT(SEARCH({"01","02","03","911","112"},A1))>0
| A | B | C | |
|---|---|---|---|
| 1 | Text | Even digit | “01”, “02”, “03”, “911”, “112” |
| 2 | Room 13 | FALSE=COUNT(SEARCH({2,4,6,8,0},A2))>0 | FALSE=COUNT(SEARCH({"01","02","03","911","112"},A2))>0 |
| 3 | Room 24 | TRUE=COUNT(SEARCH({2,4,6,8,0},A3))>0 | FALSE=COUNT(SEARCH({"01","02","03","911","112"},A3))>0 |
| 4 | Code 01-7 | TRUE=COUNT(SEARCH({2,4,6,8,0},A4))>0 | TRUE=COUNT(SEARCH({"01","02","03","911","112"},A4))>0 |
| 5 | Dial 911 now | FALSE=COUNT(SEARCH({2,4,6,8,0},A5))>0 | TRUE=COUNT(SEARCH({"01","02","03","911","112"},A5))>0 |
| 6 | Line 35 | FALSE=COUNT(SEARCH({2,4,6,8,0},A6))>0 | FALSE=COUNT(SEARCH({"01","02","03","911","112"},A6))>0 |
“Room 13” and “Line 35” have only odd digits. With quotes, “01” is searched as two characters; without them, Excel would turn it into 1 and find any 1.
Find specific number words in text
Sometimes it’s not enough to know that a cell contains digits. You need a specific number as a separate word: “1”, but not 10 or 2001. In this case, treat the number as a word and find the word in cells, or search for a list of numbers with the !SEMTools procedures.
In the example below, we search for “1” as a standalone word, ignoring numbers that merely contain it, like 10:
As you may have noticed, “2001:” didn’t match the regex. To avoid that, there is a checkbox on the !SEMTools panel to ignore punctuation. With it, words that contain characters other than letters and digits match the regex as well:
Why didn’t the 8th row match? Because after splitting into words (the space is the delimiter), the word “40-year-old” still contains letters.
Why digits aren’t detected: common mistakes
- The formula returns an error. Check the separators: in Excel with US settings, the elements of an array constant are separated by commas. A formula copied from a site for another locale, with colons or semicolons inside the braces, returns an error.
- ISNUMBER returns FALSE for a cell with digits. ISNUMBER checks whether the whole value is a number, not whether it contains digits. Use the COUNT(SEARCH(…)) formula above.
- Numbers look like numbers but don’t calculate. They are stored as text. See how to convert numbers saved as text to real numbers.
Check whole columns for digits or specific numbers right in place, without array formulas or helper columns.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary: which way to find numbers in text
I hope you found what you were looking for on this page and were able to find the digits or specific numbers in your cells. The COUNT(SEARCH(…)) formula works in any Excel version, and in Excel 365 REGEXTEST is shorter. But for a whole column, the !SEMTools procedure is faster: no syntax to remember, no formulas to fill down, the result appears right in place. If something didn’t work, feel free to write to me.
Once you’ve found the cells you need, you’ll probably want to extract, delete or change something in them. The corresponding sections of the site will help.