How to Compare Two Text Strings in Excel

Comparing text in Excel to find matches or partial matches is a common task. Whether you need to verify data entry, identify duplicates, or analyze text data, Excel provides several powerful functions to help you achieve this. This article outlines three effective methods to compare two text strings in Excel, using the IF and FIND functions, the MATCH function, and the SUMPRODUCT function with wildcards.

Using IF and FIND to Compare Text

The FIND function locates the starting position of one text string within another. When combined with the IF function, you can determine if a specific text string exists within a larger string.

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

In this formula:

• FIND(B2,A2): Searches for the text in cell B2 within cell A2. If found, it returns the starting position; otherwise, it returns an error.
• ISNUMBER(...): Checks if the result of FIND is a number (meaning a match was found). Returns TRUE if a number, FALSE if an error.
• IF(...): Returns TRUE if ISNUMBER is TRUE (match found), and FALSE if ISNUMBER is FALSE (no match found).

Leveraging the MATCH Function for Text Comparison

For Excel 2016 and later versions, the MATCH function offers a more direct approach to comparing text strings.

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

Here’s how this formula works:

• MATCH(B2,A2,0): Attempts to find an exact match for the text in cell B2 within cell A2. The 0 in the formula specifies an exact match. If found, it returns the relative position of the match; otherwise, it returns an error.
• >0: Checks if the result of MATCH is greater than 0 (indicating a match).
• IF(...): Returns TRUE if a match is found and FALSE otherwise.

Using SUMPRODUCT and Wildcards for Partial Matches

The SUMPRODUCT function, when combined with wildcards, allows you to search for partial matches within text strings.

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

Let’s break down this formula:

• *`A2:A5B2:** This part attempts to multiply each cell in the rangeA2:A5by the text inB2. Since you can't directly multiply text, this results in an error unless wildcards are used inB2. For example, to find any cell containing "apple",B2should contain“apple“`.
• --(...): Converts the errors to 0 and any non-zero values to 1. This is called double negation and coerces TRUE/FALSE values into numerical 1/0 respectively.
• SUMPRODUCT(...): Sums the resulting array of 0s and 1s.
• >0: Checks if the sum is greater than 0. A sum greater than 0 indicates at least one partial match was found.

Conclusion

Excel provides multiple ways to compare text strings, allowing you to perform various data analysis tasks. Choosing the right method depends on your specific needs: IF and FIND for basic substring searches, MATCH for exact matches, and SUMPRODUCT with wildcards for flexible partial matches. By understanding these techniques, you can effectively analyze and manipulate text data within Excel.