To test if a cell (or any text string) contains a number, you can use the FIND function together with the COUNT function. In the generic form of the formula (above), A1 represents the cell you are testing. The numbers to be checked (numbers between 0-9) are supplied as an array.
The FIND function returns the position of the text when it's found in the cell, and the #VALUE! error if not. In the example, the cell B4 contains the number "2" in the 29th position. So, if we just used this formula:
In other words, FIND checks the contents of B4 for each number and returns the result of each check as an item in the array.
After FIND returns the array, COUNT counts the items in the array. COUNT only counts numeric values, so any #VALUE! item in the array is treated as zero. COUNT will return a number greater than zero if there are any numbers in the array, and zero if not.
The last step in the formula is to compare the result the FIND and COUNT functions with zero. If there were any numbers found, the formula will return TRUE. If not, the formula will return FALSE.
If you want to do something more than just test whether a cell contains text, you can wrap the formula in an IF statement like so:
To test a cell to see if it contains one of many strings, you can use a formula based on the SEARCH , ISNUMBER and SUMPRODUCT functions. The formula in C5, copied down, is: = SUMPRODUCT ( -- ISNUMBER ( SEARCH ( things , B5 ))) > 0 where things is...
To test a range and determine if it contains one of many substrings (partial matches, specific text, etc.) you can use use a formula based on the COUNTIF function nested in the SUMPRODUCT function . How the formula works All the hard work is done by...
The Excel COUNT function returns the count of values that are numbers, generally cells that contain numbers. Values can be supplied as constants, cell references, or ranges.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.