Note this is different from checking if a cell valueis a number.
you’re free to easily perform that test with theISNUMBER function.
In this case, however, we to test if a cell valuecontainsa number, which may occur anywhere.
One solution is to use the FIND function with an array constant.
Both approaches are explained below.
FIND function
The FIND function is designed to look inside a text string for a specific substring.
If FIND finds the substring, it returns a position of the substring in the text as a number.
If the substring is not found, FIND returns a #VALUE error.
All other numbers were not found and returned #VALUE errors.
We are very close now to a final formula.
We simply need to tally up results.
COUNT only counts numeric values, and ignores errors.
In the case of cell B5, COUNT returns 3.
The last step is to check the result from COUNT and force a TRUE or FALSE result.
This formula will return “Yes” if B5 contains a number and “No” if not.
This isn’t necessarily a better approach, just a different way to solve the same problem.
error when the operation fails.
TheCOUNT functioncounts the numbers, ignoring any errors, and returns 3.
Note: as you might guess, you could easily adapt this formula tocount numbers in a text string.
Cell equals number?
Note that the formulas above are too complex if you only want to test if a cellequalsa number.
When the text is not found, FIND returns a #VALUE error.
COUNT Function
The Excel COUNT function returns a count of values that are numbers.
Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
For example, =MID(“apple”,2,3) returns “ppl”.
LEN will also count characters in numbers, but number formatting is not included.
