Excel Formula to Check If Text Contains a Matching Word
Question details
The user needs a formula to compare two columns and determine if the text from one cell exists within another cell, returning TRUE or FALSE while ignoring letter casing.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing text strings across different columns to find partial matches.
- Observed behavior
- Return TRUE if the target word is found anywhere within the referenced text string, and FALSE if no match is found.
Ensure your text data does not contain hidden leading or trailing spaces, as these invisible characters can cause the formula to return FALSE even when the word appears to match.
Use the ISNUMBER and SEARCH Functions
This is the standard, case-insensitive method to check if a specific string exists within another cell.
The SEARCH function looks for the target text and returns its numerical starting position if found. Because it is case-insensitive, 'apple' will successfully match 'Apple'. The ISNUMBER function then evaluates this result, returning TRUE if a number (position) is found, and FALSE if SEARCH results in an error.
Click on the cell where you want the TRUE/FALSE result to appear.
Type the formula: =ISNUMBER(SEARCH(A2,B2)) where A2 contains the word you are looking for, and B2 is the cell containing the larger text string being searched.
Press Enter to see the result, then click and drag the fill handle down to apply the formula to the rest of the column.
Use the IF Function for Custom Text Output
Wrap the SEARCH and ISNUMBER functions in an IF statement to return custom text strings like 'Found' or 'Not Found' instead of a basic TRUE or FALSE.
Use WPS Spreadsheet to Quickly Find Matching Text
WPS Spreadsheet fully supports Excel's ISNUMBER and SEARCH functions, allowing you to seamlessly analyze text data. It provides a highly compatible and user-friendly interface for all your formula needs without expensive subscriptions.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your text columns.
- 2. Input the formula: Select the target cell and input =ISNUMBER(SEARCH(A2,B2)).
- 3. Fill the data: Press Enter and double-click the fill handle in the bottom-right corner of the cell to apply the formula down your entire list.
Frequently Asked Questions
How can I make the formula case-sensitive?
To make the text search case-sensitive, replace the SEARCH function with the FIND function. The updated formula would be =ISNUMBER(FIND(A2,B2)).
Can I check if a cell contains one of multiple words?
Yes, you can use the SUMPRODUCT function combined with ISNUMBER and SEARCH. For example, entering =SUMPRODUCT(--ISNUMBER(SEARCH({"word1","word2"}, B2)))>0 will return TRUE if either 'word1' or 'word2' is found.
Why does my formula return FALSE even when the word is clearly there?
This is often due to accidental extra spaces in your reference cells. Try wrapping the reference cell with the TRIM function to remove hidden spaces, like this: =ISNUMBER(SEARCH(TRIM(A2),B2)).
