Check A list of Texts In String

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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube