This article will talk about how to check if a given cell contains any numbers in Microsoft Excel Spreadsheet or Google Sheets. How to quickly check if a cell contains a number? When you have a small number of cells that need to filter out the cells that contain numbers, you can manually select them, and that is acceptable; however, when there are thousands of cells, it is not wise to filter the cells with numbers by manually.
Below we will see how to quickly check whether the cell contains numbers by Excel or google sheets formula, when the cell contains numbers, return TRUE, otherwise return FALSE.
Table of Contents
Cell Contains Number in Google Sheets & Excel
In Excel or google sheets, if a cell contains letters and numbers, then we can assume that it is a text cell.
Generic Formula
If you need to test whether a given text cell contains any of the numbers, then you can use the FIND function in combination with the COUNT function consisting of nested formulas. The general formula is as follows.
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A3))>0
where A3 is the text cell reference you want to test, the formula will go to check whether the text cell A3 contains the given number, and the number to be checked in the form of an array nested into the FIND formula.
Formula Explanation
= FIND({0,1,2,3,4,5,6,7,8,9},A3))
First, we need to use the FIND function in the given text cell to find out the location of the corresponding number in the text, if the number is found, then it will return the location of the number in the text string, if not found, then return # VALUE!
For example: the formula FIND (“2”, A3), the number 2 is in the position of 7 in cell A3, then the FIND function will return 7. Just see the below screenshot.
Since we pass the FIND function an array containing numbers, the FIND function will return a similar array as the return value.
{8,9,7,10,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
Note: You can also replace the FIND function with the SEARCH function to find the position of a number in a text string, so the SEARCH function can return the same result.
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A3))
The array result returned by the FIND formula is passed to the COUNT function as an argument, and the COUNT function counts the items in the array. Since the COUNT function only counts numeric values, all # VALUE! items in the array will be considered as 0.
When there are no numeric items in the array, the COUNT formula will return 0, otherwise it will return a value greater than 0.
For example: we use COUNT formula to count the array values {1,3,#VALUE!}, it returns 2, see the screenshot below:
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A3))>0
When we count the number of numbers contained in the text cell, then we need to compare the number of numbers is greater than 0, if greater than 0, then return TRUE, if equal to 0, then return FALSE.
We just need to compare the value returned by the COUNT formula with 0, see the below example:
=COUNT({1,2,#VALUE!})>0
As you can see from the screenshot above, the COUNT function returns 2, which is greater than 0, so it returns TRUE.
IF Nested Formulas
If you want to make the return value look more meaningful, then you can embed the COUNT formula above into the IF function so that it returns YES or No, see the following formula.
=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A3))>0,"yes","NO")
Check Cell Contains Number in Google Sheets
You can use the generic formula above in Google Sheets to check if a text cell contains a number.
Note: Now you can pass array values directly in Google sheet, so you don’t need to use the CTRL+SHIFT+ENTER shortcut to turn the formula into an array formula. Of course, you can use the shortcut to convert it to an array formula and the result is the same.
=ARRAYFORMULA(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A3))>0)
See the below screenshot:
You can see that the returned value is the same as the formula above.
Related Functions
- Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…. - Excel FIND function
The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
- Excel COUNT function
The Excel COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…