Sometimes you need to find numbers in a range of Excel cells, but they may be mixed with text, which makes the task more difficult.
First, let’s clarify the terminology. To “detect” usually means determining whether a certain character exists in a cell or not. The result of such a function is a Boolean value — either TRUE or FALSE.
If you’re looking to do more than simply detect digits — like extract or remove them — check out these guides:
- How to extract digits from text in Excel
- How to delete digits from cells
Digits are a collective group of 10 characters (0–9). To check whether a cell contains any digits, you’d technically need to check for each one separately — 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9.
One straightforward example of this is shown in the description of the IF function.
But is there a way to replace ten separate checks with a single formula?
Unfortunately, Excel doesn’t offer a built-in function for this, but you can combine multiple formulas, including an array formula, to get the result.
Formula to Check for Digits in a Cell
The following formula will return TRUE if any digit is found in a cell, FALSE otherwise. It’s not the simplest syntax, but it gets the job done effectively.
=COUNT(SEARCH({1:2:3:4:5:6:7:8:9:0}, A1))>0
The logic here is the same as in the formula used for detecting Latin or Cyrillic characters.
“Find Digits” Macro in !SEMTools
The easiest way to do this is with a built-in macro available after you install !SEMTools. Just copy your source column, apply the macro, and see the result — done in place!
How to Find Only Certain Digits in Excel
If you need to find specific digits — such as only even or only odd ones — there are two approaches: using regular expressions or modifying the earlier formula.
Find Specific Digits Using Regex in !SEMTools
With !SEMTools, you can use regular expressions to define the digits you’re looking for. Just enter the digits in square brackets and press OK. You can use the same tool to find Latin characters or detect uppercase letters.
Find Specific Digits Using a Formula
You can adjust the earlier array formula to look for specific digits — for example, only even numbers — using COUNT and SEARCH functions:
=COUNT(SEARCH({2:4:6:8:0}, A1))>0
This same technique can be extended to detect sequences of digits of any length:
=COUNT(SEARCH({01:02:03:911:112}, A1))>0
Find Specific Number Words in Text
Sometimes, it’s not enough to find whether a cell contains digits — you may need to find a specific number as a separate word. In such cases, treat the number as a word and use the Find Word in Cell tool or search from a list of numbers using !SEMTools procedures.
In the example below, we search for “1” as a standalone word, ignoring numbers that merely contain it (like 10).
Convert Numbers to Text
Sometimes, you need to not only find numbers in text but convert them to text and even apply grammar rules to the result. See these guides:
- Convert number to words in Excel
- Decline numbers grammatically in Excel
Conclusion
Hopefully this guide helped you understand how to find the digits or specific numbers you’re looking for in Excel cells. If not, feel free to reach out to the author of this add-in and website.
Once you’ve located the relevant values, you’ll probably want to extract, delete, or transform them — be sure to visit those sections to learn how!
Need to quickly find digits in Excel cells?
!SEMTools helps you do that — and hundreds of other tasks — in just a few clicks!
This post is also available in RU.