Return to Excel Formulas List

Check if Cell Contains Any Number – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial demonstrates how to check if a cell contains any number in Excel and Google Sheets.

if cell contains any number Main Function

Cell Contains Any Number

In Excel, if a cell contains numbers and letters, the cell is considered a text cell. You can check if a text cell contains any number by using the COUNT and FIND Functions.

CONTAINS NUMBER

The formula above checks for the digits 0–9 in a cell and counts the number of discrete digits the cell contains. Then it returns TRUE if the count is positive or FALSE if it is zero.

Let’s step through each function below to understand this example.

FIND a Number in a Cell

First, we use the FIND Function. The FIND Function finds the position of a character within a text string.

FIND

In this example, we use an array of all numerical characters (digits 0–9) and find each one in the cell. Since our input is an array – in curly brackets {} – our output is also an array. The example above shows how the FIND Function is performed ten times on each cell (once for each numerical digit).

If the number is found, it’s position is output. Above you can see the number “1” is found in the 3rd position in the first row and “4” is found in the 6th position in the 2nd row.

If a number is not found, the #VALUE! Error is displayed.

Note: The FIND and SEARCH Functions return the same result when used to search for numbers. Either function can be used.

COUNT the Number of Digits

Next, we count the non-error outputs from the last step. The COUNT Function counts the number of numeric values found in the array, ignoring errors.

COUNT

Test the Number Count

Finally, we need to test whether the result from the last step is greater than zero. The formula below returns TRUE for non-zero counts (where the target cell contains a number) and FALSE for any zero counts.

COUNT-01

Combining these steps gives us our initial formula:

CONTAINS NUMBER

Check if Cell Contains Specific Number

To check if a cell contains a specific number, we can use the FIND or SEARCH Function.

SPECIFIC NUMBER

In this example we use the FIND Function to check for the number 5 in column B. It returns the position of the number 5 in the cell if it is found and a VALUE error if “5” is not found.

Check if Cell Contains Any Number – Google Sheets

These formulas work the same in Google Sheets as in Excel. However, you need to press CTRL + SHIFT + ENTER for Google Sheets to recognize an array formula.

Alternatively, you could type “ArrayFormula” and put the formula in parentheses. Both methods produce the same result.

if cell contains any number Google Function