How to Check if a string contains one of many texts in Excel

Generic formula to check a list of texts in a String (use CTRL + SHIFT + ENTER)

=SUM(--ISNUMBER(SEARCH(list of strings, string)))>0

25
Scenario:
You have a survey data in which people have answered some questions. The data entry operator entered all data as it is. See the image below.
26
Now you have to know who mentioned any of these 3 colors Red, Blue and Green.
27
Write this formula in adjacent cell and hit CTRL+SHIFT+ENTER on the keyboard.

=SUM(--ISNUMBER(SEARCH($F$2:$F$4,A2)))>0

Drag it down and you have your answers. Now how many of your audience has mentioned these three colors in there statements.
28
How it Works

  • CTRL + SHIFT + ENTER
    =SUMPRODUCT(--ISNUMBER(SEARCH($F$2:$F$4,A2)))>0
  • SEARCH($F$2:$F$4,A2) : The SEARCH looks for each value in the range $F$2:$F$4 and returns an array of found locations or #VALUE! Errors. For cell A2 it returns {#VALUE!;#VALUE!;#VALUE!} for A3 it will return {#VALUE!;#VALUE!;1}.
  • ISNUMBER(SEARCH($F$2:$F$4,A2)) : ISNUMBER checks if supplied value in array is a number or not, if its number it returns TRUE else FALSE. For cell A2 it returns {FALSE;FALSE;FALSE} for A3 it will return {FALSE;FALSE;TRUE}.
  • --ISNUMBER(SEARCH($F$2:$F$4,A2)) : The double unary operator “--” converts TRUE into 1 and FALSE to 0. For cell A2 it returns {0;0;0} and for A3 it will return {0;0;1}.
  • SUM(--ISNUMBER(SEARCH(list of strings, string))) : SUM function sum the array. For cell A2 it returns 0 and for A3 it will return 1.
  • SUM(--ISNUMBER(SEARCH(list of strings, string)))>0 : Finally we check if sum of array is greater than 0 or not. If greater than 0, it means there is at least one mention of given colors and returns TRUE else FALSE.
  • Using this function you can check multiple texts or say substrings in a string in one stance. To learn more about amazing function of EXCEL 2016, 2013 and 2010 or older go to our home page. We have a large list of useful excel articles.

Related Articles:

Sum if cells contain specific text

Sum if cell contains text in other cell in Excel

Count Cells that contain specific text

Split Numbers and Text from String in Excel

Highlight cells that contain specific text

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity

How to use the VLOOKUP Function in Excel

How to use the COUNTIF function in Excel

How to use the SUMIF Function in Excel

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.