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:
We want to test each cell in B5:B11 to see if it contains any of the strings in the named range things (E5:E9). The formula we're using in C5, copied down, is: = SUMPRODUCT ( -- ISNUMBER ( SEARCH ( things , B5 ))) > 0 This formula is based on a...
All the hard work is done by the COUNTIF function, which is configured to count the values in the named range "substrings" that appear the named range "rng" with like this: COUNTIF ( rng , "*" & substrings & "*" )) By...
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.