Cell Contains Number

 check Cell Contains Number

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.

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
 check Cell Contains Number

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.

 check Cell Contains Number

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!}
 check Cell Contains Number

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:

 check Cell Contains Number

=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
 check Cell Contains Number

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

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.

 check Cell Contains 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:

 check Cell Contains Number

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…
Related Posts

Phone Number Format in Excel

Sometimes when we import phone numbers from external sources, the formatting of the phone numbers is removed. Even if in some situations the phone numbers are not imported from external resource, sometimes the phone numbers in worksheet are not displayed ...

If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of ...

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Add Row Numbers And Skip Blanks in Excel

Do you ever have to input a list of numbers into a spreadsheet, and some of the cells are blank? It can be difficult to keep track of what number is in which cell when you have to scroll up ...

Assigning Points based on Late Time

It is shown in this lesson how to utilize the IF function in Excel to allocate points based on the amount of time that has passed. If you intend to pursue along this guide, you may do so by downloading ...

Sidebar