Compare Text in Excel: 3 Easy Formulas

Comparing text in Excel is a common task, whether you’re cleaning data, verifying information, or simply need to check if one cell’s content is part of another. Excel offers several functions to perform these text comparisons efficiently. This article will guide you through three straightforward formulas to Compare Text In Excel, catering to various comparison needs.

1. The IF and FIND Formula: Case-Insensitive Substring Check

This combination is excellent for determining if a specific text string (substring) exists within a larger text string in another cell. The FIND function locates the starting position of one text string inside another, and IF then uses this information to return a TRUE or FALSE result.

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

• Breakdown:

  • FIND(B2,A2): This function searches for the text in cell B2 within the text in cell A2. If found, it returns the starting position of B2 within A2 as a number. If not found, it returns a #VALUE! error. Crucially, FIND is case-insensitive. “apple” and “Apple” will be considered the same.
  • ISNUMBER(...): This function checks if the result of FIND is a number. It returns TRUE if it’s a number (meaning the text was found), and FALSE if it’s an error (text not found).
  • IF(...,TRUE,FALSE): The IF function takes the TRUE or FALSE result from ISNUMBER and directly returns TRUE if the text is found and FALSE if not.

• Example: If cell A2 contains “The quick brown fox jumps over the lazy dog Apple” and cell B2 contains “apple”, the formula will return TRUE.

2. The IF and MATCH Formula: Another Substring Approach (Excel 2016+)

The MATCH function can also be used for text comparison, particularly in newer versions of Excel (2016 and later). It’s similar to FIND in that it can locate a substring, and when combined with IF, provides a TRUE/FALSE outcome.

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

• Breakdown:

  • "*"&B2&"*": This part uses wildcards (*) with the text from cell B2. The asterisk wildcard represents any sequence of characters. By placing asterisks before and after B2, we are effectively searching for B2 anywhere within A2.
  • MATCH(...,A2,0): The MATCH function then tries to find this wildcard-enhanced text within cell A2. The 0 argument specifies an exact match, but with the wildcards, it’s effectively looking for a “contains” match. If found, MATCH returns the relative position of the match (a number). If not found, it returns a #N/A error.
  • ISNUMBER(...): Similar to the first formula, ISNUMBER checks if MATCH returned a number (text found) or an error (text not found).
  • IF(...,TRUE,FALSE): The IF function returns TRUE if text is found and FALSE otherwise.

• Example: If A2 is “The quick brown fox jumps over the lazy Cat” and B2 is “Cat”, this formula will return TRUE.

3. The SUMPRODUCT and Wildcard Formula: Comparing Across Ranges

For comparing a single text string against a range of cells, the SUMPRODUCT formula with wildcards is a powerful option. This is useful when you need to check if a specific text exists in any cell within a list.

=SUMPRODUCT(--(ISNUMBER(SEARCH(B2,A2:A5))))>0

• Breakdown:

  • A2:A5: This represents the range of cells you want to search within. Adjust this range as needed for your data.
  • SEARCH(B2,A2:A5): The SEARCH function is similar to FIND but also case-insensitive. It looks for the text in B2 within each cell in the range A2:A5. It returns the starting position if found and an error if not.
  • ISNUMBER(...): Checks if SEARCH found the text in each cell of the range, returning TRUE or FALSE for each cell.
  • --(...): The double negative (--) converts the TRUE and FALSE values from ISNUMBER into 1s and 0s, respectively. This is necessary for SUMPRODUCT to work correctly in this context.
  • SUMPRODUCT(...): This function sums up the resulting 1s and 0s. If the text in B2 is found in at least one cell within the range A2:A5, the sum will be greater than 0.
  • ...>0: Finally, we check if the SUMPRODUCT result is greater than 0. If it is, the formula returns TRUE (text found in the range); otherwise, it returns FALSE.

• Example: If cells A2:A5 contain various text strings, and B2 contains “Zebra”, and none of the cells in A2:A5 contain “Zebra”, the formula will return FALSE.

Choosing the Right Formula

• For simple, case-insensitive checks of whether one text string is part of another, the IF and FIND formula is often the most straightforward.
• The IF and MATCH formula with wildcards provides a similar functionality and might be preferred by users familiar with MATCH.
• When you need to compare a text string against a range of cells, the SUMPRODUCT formula is the most versatile.

Remember to adjust the cell references in these formulas to match your specific Excel sheet layout. These methods offer flexible and efficient ways to compare text in Excel, simplifying your data analysis and manipulation tasks.