Effortlessly Compare Text in Excel with “Contains Text”: 3 Proven Methods

Microsoft Excel is a powerful tool for data analysis, and often you need to compare text strings to see if one contains another. This “contains text” comparison is crucial for tasks like data validation, cleaning, and categorization. Instead of manually sifting through rows of data, Excel offers efficient formulas to automate this process.

This article will guide you through three distinct and effective methods to compare text in Excel using the “contains text” logic. Whether you’re a beginner or an experienced Excel user, these techniques will empower you to quickly identify cells containing specific text, saving you time and enhancing your data manipulation capabilities.

Let’s dive into each method with clear explanations and examples:

1. Leveraging the IF and FIND Functions for Text Comparison

This method combines the IF and FIND functions to perform a “contains text” check. The FIND function is instrumental here as it locates the starting position of one text string within another. If the text is found, FIND returns a number representing the starting position; otherwise, it returns an error. We then use ISNUMBER to check if FIND returned a number (meaning the text was found), and IF to translate this into a TRUE/FALSE result.

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

• Breakdown:
  • FIND(B2,A2): This part searches for the text in cell B2 (the text you want to find) within the text in cell A2 (the text to search within).
  • ISNUMBER(...): This function checks if the result of the FIND function is a number. If FIND successfully locates the text, it returns a number, and ISNUMBER returns TRUE. If FIND doesn’t find the text, it returns an error, and ISNUMBER returns FALSE.
  • IF(...,TRUE,FALSE): The IF function then uses the TRUE or FALSE result from ISNUMBER. If ISNUMBER is TRUE (text found), the IF function returns TRUE. If ISNUMBER is FALSE (text not found), the IF function returns FALSE.

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. If B2 contains “Zebra”, it will return FALSE.

2. Utilizing the MATCH Function (Excel 2016 and Later Versions)

For users with Excel 2016 or later versions, the MATCH function offers another concise way to achieve “contains text” comparison. MATCH is typically used to find the position of an item in a range, but with a slight adjustment, it can effectively check for text inclusion.

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

• Explanation:
  • MATCH(B2,A2,0): This attempts to find an exact match for the text in B2 within A2. While it’s searching for an exact match, in the context of “contains text”, if B2 is part of A2, MATCH will find a “match” and return its relative position within the string (which will be greater than 0). The 0 as the third argument specifies an exact match type, but here we are leveraging its behavior when the lookup value is part of the within-text value.
  • ...>0: This checks if the result of MATCH is greater than 0. If MATCH finds the text (even as part of a larger string), it returns a positive number (the position), making the condition TRUE. If the text isn’t found, MATCH typically returns an error, but in this formula construction, it may return other non-positive values or errors which will be evaluated to FALSE by the IF condition.
  • IF(...,TRUE,FALSE): Similar to the previous method, IF translates the boolean result into TRUE or FALSE.

Note: This method is available in Excel 2016 and subsequent versions. Ensure your Excel version is compatible.

3. Employing SUMPRODUCT with Wildcards for Flexible Text Matching

The SUMPRODUCT function, combined with wildcards, provides a more versatile approach, especially when dealing with ranges of cells. Wildcards are special characters that allow for pattern matching in text strings. In Excel, the asterisk (*) wildcard represents any sequence of characters.

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

Alternatively, for older versions or slightly different behavior (depending on version and settings):

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

*(Note: The second formula with `A2:A5B2might have limitations or require careful context due to potential implicit type conversions and is less robust than usingSEARCHandISNUMBER` for explicit text comparison.)**

Let’s break down the more robust SEARCH and ISNUMBER version:

• SEARCH(B2,A2): Similar to FIND, SEARCH locates text within another text string. However, SEARCH is not case-sensitive and allows for wildcards in the search string (though we are not using wildcards in B2 in this example, but searching for exact content of B2 within A2). It returns the starting position if found, and an error if not.
• ISNUMBER(...): Checks if SEARCH returned a number (text found).
• --(ISNUMBER(SEARCH(B2,A2))): The double negative (--) converts the TRUE or FALSE result from ISNUMBER into 1 or 0, respectively. This is necessary for SUMPRODUCT to work correctly.
• SUMPRODUCT(...): In this case, SUMPRODUCT is used to sum up the results (1s and 0s). If the text in B2 is found within A2, SUMPRODUCT will sum at least a 1, resulting in a sum greater than 0.
• ...>0: Checks if the sum is greater than 0. If yes, it means the text was found at least once, and the formula returns TRUE; otherwise, it returns FALSE.

Example: Using the first SUMPRODUCT formula with SEARCH: If cell A2 contains “The quick brown fox jumps over the lazy dog Apple” and cell B2 contains “Apple”, the formula returns TRUE. If B2 contains “Zebra”, it returns FALSE.

Choosing the Right Method for Your Needs

• For simplicity and basic “contains text” checks, the IF and FIND combination is often the easiest to understand and implement.
• If you are using Excel 2016 or later and prefer a slightly more concise formula, the MATCH function method is a good option.
• When dealing with ranges of cells or requiring case-insensitive search, the SUMPRODUCT with SEARCH method offers more flexibility.

All three methods effectively address the need to compare text in Excel and determine if a cell “contains text”. Choose the formula that best suits your Excel version, complexity requirements, and personal preference. Start applying these formulas to streamline your Excel tasks and gain deeper insights from your data!