How to Compare Text Values in Excel

Comparing text values in Excel to determine if a cell contains specific text is a common task. Whether you need to find a specific word in a sentence or check if a cell contains a particular substring, Excel provides several functions to accomplish this. This guide will outline three effective methods to compare text values in Excel, using formulas that cater to different Excel versions and user preferences.

Three Methods to Compare Text in Excel

Here are three distinct formulas to help you compare text values within Excel:

1. Comparing Text Using IF and FIND Functions

The IF and FIND functions combined provide a reliable way to check if a specific text string exists within another cell.

=IF(ISNUMBER(FIND(B2,A2)),TRUE,FALSE)

How it Works:

• The FIND function searches for the text in cell B2 within cell A2. If the text is found, FIND returns its starting position (a number). If not found, it returns an error value.
• The ISNUMBER function checks if the result of FIND is a number. If it is (meaning the text was found), ISNUMBER returns TRUE. Otherwise, it returns FALSE.
• The IF function then uses the result of ISNUMBER to determine the final output. If ISNUMBER is TRUE (text found), IF returns TRUE. If ISNUMBER is FALSE (text not found), IF returns FALSE.

2. Comparing Text with the MATCH Function (Excel 2016 and Later)

For users with Excel 2016 or later versions, the MATCH function offers a slightly more concise approach.

=IF(MATCH(B2,A2,0)>0,TRUE,FALSE)

How it Works:

• The MATCH function searches for the exact text from cell B2 within cell A2. The 0 in the formula specifies an exact match.
• If found, MATCH returns the position of the text within the cell (a number greater than 0). If not found, it returns an error.
• Similar to the previous method, the IF function checks if the result of MATCH is greater than 0. If true, IF returns TRUE (text found). Otherwise, it returns FALSE (text not found).

3. Using SUMPRODUCT with Wildcards for Partial Matches

The SUMPRODUCT function, combined with wildcards, offers a powerful way to check for partial matches within a range of cells.

=SUMPRODUCT(--(A2:A5*B2))>0

How it Works:

• This formula assumes your data is in cells A2:A5 and the text you’re searching for is in B2. You can adjust the range as needed.
• The * wildcard represents any sequence of characters. By using *B2*, the formula checks if any cell in the range A2:A5 contains the text in B2, regardless of its position within the cell.
• SUMPRODUCT multiplies corresponding components in the given arrays and returns the sum of those products. The double negative (--) converts TRUE/FALSE values to 1/0 respectively.
• Finally, the formula checks if the sum is greater than 0. If it is, at least one cell in the range contains the text from B2, and the formula returns TRUE. Otherwise, it returns FALSE.

Conclusion

These three methods provide flexible ways to compare text values in Excel. Choose the method that best suits your specific needs and Excel version. Understanding these functions will empower you to efficiently analyze and manipulate text data within your spreadsheets. Remember to adjust cell references and ranges to match your data set.