At times you may want to check if cells in an Excel worksheet contain numeric characters. This tutorial shows you one technique for checking whether a cell contains a number.
Use a Combination of COUNT and IF Functions
Suppose we have the following dataset of user passwords in range A2:A6.
We want to check if the passwords in range A2:A6 contain numeric characters. We use a formula that returns TRUE if the password in a cell in the range contains a number or FALSE if it does not contain a numeric character.
We use the following steps:
Select cell B2 and type in the following formula:
|
1 |
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0 |
Press Enter on the keyboard or click the Enter button on the Formula Bar.
Double-click or drag down the fill handle to copy the formula down the column.
Explanation of the Formula
|
1 |
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0 |
The formula uses the COUNT and FIND functions.
The COUNT function counts the number of cells in a range that contain numbers.
The FIND function returns the starting position of one text string within another text string. It is case-sensitive.
FIND({0,1,2,3,4,5,6,7,8,9},A2). This part of the formula returns the array {#VALUE!;1;2;3;4;5;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
The FIND function returns the position of the search value within the value being searched. It returns the #VALUE! error if it does not find the search value within the value searched in cell A2.
COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2)). This part of the formula returns the value 5.
The COUNT function only counts the numbers. A numeric value is equated to 1 and the #VALUE! error is equated to 0 (zero). The COUNT function returns a value greater than zero if a numeric character is found in the array.
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0. The value returned by the COUNT function is compared to zero. If it is greater than zero the value TRUE is returned, otherwise, the value FALSE is returned. In this case, the formula returns TRUE because the value 5 is greater than 0 (zero).
Conclusion
This tutorial showed how the COUNT and FIND functions can be used in a formula to check if a cell contains a number.