Formula to compare text in 2 cells

TMF5225 

There are several formulas you can use to achieve this, depending on your preference and Excel version. Here are three options:

1. Using the IF and FIND functions:

• Explanation: This formula checks if the text in cell B2 exists within the text of cell A2 using the FIND function. If found, ISNUMBER(FIND) returns TRUE and the whole IF statement returns TRUE. If not found, FIND returns an error, ISNUMBER(FIND) returns FALSE, and the whole IF statement returns FALSE.

2. Using the MATCH function (Excel 2016 and later):

• Explanation: This formula uses the MATCH function to find the position of the text in cell B2 within the text of cell A2. If found, MATCH returns the position (greater than 0), and the IF statement returns TRUE. If not found, MATCH returns 0, and the IF statement returns FALSE.

3. Using the SUMPRODUCT function with wildcards:

• Explanation: This formula combines the SUMPRODUCT and wildcard (*) functions. It assumes your data is in cells A2:A5 and B2. It multiplies each cell in A2:A5 by the text in B2 using wildcards to account for the entire text (not just the beginning). SUMPRODUCT then adds these products and checks if the sum is greater than 0. If any text in A2:A5 contains the text in B2, the sum will be greater than 0 and the formula returns TRUE. Otherwise, it returns FALSE.

These formulas will return True in your example for: "Apple" in "The quick brown fox jumps over the lazy dog Apple", "Cat" in "The quick brown fox jumps over the lazy Cat", and False for "Zebra" in both cases.

Choose the formula that best suits your preference and Excel version. You can adjust the cell references and ranges as needed for your specific data.

I hope the information above has been helpful. Please consider remembering me in your prayers. May peace be upon you

TMF5225 You will need to use SEARCH() function.

=ISNUMBER(SEARCH(B1,A1))