How to Compare Two Cells in Excel with Text

Comparing two cells in Excel to see if one text string contains another is a common task. Excel provides several functions to accomplish this efficiently. Here are three effective methods:

1. Using the IF and FIND Functions

The FIND function locates the starting position of one text string within another. When combined with the IF function, you can determine if the text exists and return a TRUE or FALSE result.

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

Explanation:

• FIND(B2,A2): This searches for the text in cell B2 within cell A2. If found, it returns the starting position as a number. If not found, it returns an error value.
• ISNUMBER(...): This checks if the result of FIND is a number (meaning the text was found). It returns TRUE if a number and FALSE if an error.
• IF(...): This returns TRUE if ISNUMBER is TRUE (text found) and FALSE otherwise.

2. Using the MATCH Function (Excel 2016 and later)

The MATCH function searches for a specific item within a range and returns its relative position. For comparing text within a single cell, use it with a wildcard.

=IF(ISNUMBER(MATCH("*"&B2&"*",A2,0)),TRUE,FALSE)

Explanation:

• "*"&B2&"*": This creates a text string with asterisks (*) before and after the text in cell B2. The asterisks act as wildcards, allowing MATCH to find the text anywhere within cell A2.
• MATCH(...,A2,0): This searches for the wildcard string within cell A2 with an exact match (match_type = 0). If found, it returns the position. If not found, it returns an error value.
• ISNUMBER(...): Checks if the result of MATCH is a number, indicating a match was found. returns TRUE for a number and FALSE for an error.
• IF(...): Returns TRUE if a match is found (ISNUMBER is TRUE) and FALSE otherwise.

3. Using the SEARCH Function with Wildcards

Similar to FIND, the SEARCH function is not case-sensitive. It can be combined with wildcards and the ISNUMBER function for flexible text comparison.

=IF(ISNUMBER(SEARCH("*"&B2&"*",A2)),TRUE,FALSE)

Explanation:

This formula works similarly to the MATCH example but uses SEARCH instead. It’s less strict about matching the entire cell content since it allows wildcards and performs a case-insensitive search.

Conclusion

These three methods provide reliable ways to compare two cells in Excel containing text and determine if one string is contained within the other. Choose the formula that best suits your needs and Excel version. Remember to adjust cell references as needed for your specific data. Using these functions will enable you to efficiently analyze and manipulate text data within your spreadsheets.