How to Use Excel Formulas to Find Words in a List of Text Data

Sometimes, you may want to check if a cell contains any word from a list of words, and return the matching word or some other value. For example, you may have a list of product names and you want to categorize them based on some keywords.

To do this, you need to use a formula that can search for multiple text strings within a single text string, and return the position or the value of the matching text. There are different ways to achieve this, but one common method is to use the SEARCH function, which can find one text string within another text string, and return the number of the starting position of the first character of the text string.

The SEARCH function has the following syntax:

=SEARCH (find_text, within_text, [start_num])

where:

• find_text is the text you want to find.
• within_text is the text where you want to search for the find_text.
• start_num is an optional argument that specifies the character number in the within_text where you want to start the search. The default value is 1.

The SEARCH function is case-insensitive, which means it will treat uppercase and lowercase letters as the same. If you want to perform a case-sensitive search, you can use the FIND function instead, which has the same syntax as the SEARCH function.

The SEARCH function will return a number if the find_text is found in the within_text, or an error value (#VALUE!) if the find_text is not found.

Procedures

To find words in a list of text data in Excel using formulas, you need to follow these steps:

1. Prepare your data in a worksheet, such as the product names in column A and the keywords in column E.
2. Enter the formula in the cell where you want to display the result, such as cell C2.
3. Use the SEARCH function to search for each keyword in the product name, and return an array of numbers or errors. For example, =SEARCH (E$2:E$6,A2) will search for the keywords in E2:E6 in the product name in A2, and return an array like {#VALUE!;#VALUE!;#VALUE!;4;#VALUE!}.
4. Use the ISNUMBER function to convert the array of numbers or errors into an array of TRUE or FALSE values, indicating whether the keyword is found or not. For example, =ISNUMBER (SEARCH (E$2:E$6,A2)) will return an array like {FALSE;FALSE;FALSE;TRUE;FALSE}.
5. Use the MATCH function to find the position of the first TRUE value in the array, which corresponds to the position of the first matching keyword in the list. For example, =MATCH (TRUE,ISNUMBER (SEARCH (E$2:E$6,A2)),0) will return 4, which means the fourth keyword in E2:E6 is the first one that matches the product name in A2.
6. Use the INDEX function to return the value of the keyword at the position found by the MATCH function. For example, =INDEX (E$2:E$6,MATCH (TRUE,ISNUMBER (SEARCH (E$2:E$6,A2)),0)) will return “Shoes”, which is the value of the fourth keyword in E2:E6.
7. Enter the formula as an array formula by pressing Ctrl + Shift + Enter. This will make the formula evaluate the arrays inside the functions and return the final result.
8. Copy the formula to the other cells in the column to get the results for the other product names.

Explanation

The formula to find words in a list of text data in Excel using formulas is based on the following logic:

• The SEARCH function can search for one text string within another text string, and return the number of the starting position of the first character of the text string. For example, =SEARCH ("Shoes","Blue Shoes") will return 6, because the word “Shoes” starts at the sixth character of the text “Blue Shoes”.
• The SEARCH function can also accept an array of text strings as the find_text argument, and return an array of numbers or errors as the result. For example, =SEARCH ({"Shoes","Socks","Hat"},"Blue Shoes") will return {6;#VALUE!;#VALUE!}, because only the word “Shoes” is found in the text “Blue Shoes”, and the other words are not found.
• The ISNUMBER function can test if a value is a number or not, and return TRUE or FALSE as the result. For example, =ISNUMBER (6) will return TRUE, and =ISNUMBER (#VALUE!) will return FALSE.
• The ISNUMBER function can also accept an array of values as the argument, and return an array of TRUE or FALSE values as the result. For example, =ISNUMBER ({6;#VALUE!;#VALUE!}) will return {TRUE;FALSE;FALSE}, because only the first value is a number, and the other values are errors.
• The MATCH function can find the position of a value in a range or an array, and return the relative position of the value as the result. For example, =MATCH (6,{2;4;6;8},0) will return 3, because the value 6 is the third value in the array {2;4;6;8}.
• The MATCH function can also accept a logical value (TRUE or FALSE) as the lookup value, and find the position of the first occurrence of the logical value in the range or array. For example, =MATCH (TRUE,{FALSE;FALSE;TRUE;TRUE},0) will return 3, because the first TRUE value is the third value in the array {FALSE;FALSE;TRUE;TRUE}.
• The INDEX function can return the value of a cell in a range or an array, based on the row and column numbers specified. For example, =INDEX ({2;4;6;8},3) will return 6, because the value 6 is the third value in the array {2;4;6;8}.
• The INDEX function can also accept an array of values as the array argument, and return the value of the element at the position specified by the row number argument. For example, =INDEX ({"Shoes";"Socks";"Hat"},3) will return “Hat”, because the value “Hat” is the third value in the array {“Shoes”;“Socks”;“Hat”}.

By combining these functions, we can create a formula that can find words in a list of text data in Excel using formulas. The formula works as follows:

• First, we use the SEARCH function to search for each keyword in the product name, and return an array of numbers or errors. For example, if the product name is “Blue Shoes” and the keywords are “Shoes”, “Socks”, and “Hat”, the SEARCH function will return {6;#VALUE!;#VALUE!}.
• Next, we use the ISNUMBER function to convert the array of numbers or errors into an array of TRUE or FALSE values, indicating whether the keyword is found or not. For example, the ISNUMBER function will return {TRUE;FALSE;FALSE}.
• Then, we use the MATCH function to find the position of the first TRUE value in the array, which corresponds to the position of the first matching keyword in the list. For example, the MATCH function will return 1, which means the first keyword in the list is the first one that matches the product name.
• Finally, we use the INDEX function to return the value of the keyword at the position found by the MATCH function. For example, the INDEX function will return “Shoes”, which is the value of the first keyword in the list.

Example

To illustrate the formula with a real example, let’s assume we have the following data in a worksheet:

We want to use the formula to find the words in the list of text data in column A, and return the matching words in column B. The formula we need to enter in cell B2 is:

=INDEX (E$2:E$6,MATCH (TRUE,ISNUMBER (SEARCH (E$2:E$6,A2)),0))

and press Ctrl + Shift + Enter to enter it as an array formula. Then we copy the formula to the other cells in column B. The result will look like this:

As you can see, the formula has successfully found the words in the list of text

Other option

There are other ways to find words in a list of text data in Excel using formulas, such as:

• Using the SUMPRODUCT and COUNTIF functions to count the number of occurrences of each keyword in the product name, and return the keyword with the highest count. For example, the formula =INDEX (E$2:E$6,MATCH (MAX (SUMPRODUCT (COUNTIF (A2,"*"&E$2:E$6&"*"))),SUMPRODUCT (COUNTIF (A2,"*"&E$2:E$6&"*")),0)) will return the same result as the previous formula, but with a different logic.
• Using the FILTER and ISNUMBER functions to filter the list of keywords based on whether they are found in the product name, and return the first value of the filtered list. For example, the formula =FILTER (E$2:E$6,ISNUMBER (SEARCH (E$2:E$6,A2)),"") will return the same result as the previous formula, but with a different logic. This formula requires Excel 365 or later versions.

These are some alternative formulas that can achieve the same goal, but they may have different advantages and disadvantages depending on the situation. You can try them out and see which one works best for you.