Find a Partial Word Match Between Two Cells in Excel
Question details
Determine whether any word from one cell appears in another cell without using Kutools.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Compare pairs of names or phrases, such as Roger Johnson and Johnson Motors, to find a shared word.
- Observed behavior
- The desired result is TRUE when a word from the first cell appears in the second cell and FALSE otherwise.
Place the first text in column A and the text to compare in column B. Check whether your Excel version supports TEXTSPLIT; if not, use the helper-column method for cells containing up to two words.
Use TEXTSPLIT and SEARCH to Check Each Word in Excel
Use this formula in Excel versions that support TEXTSPLIT to test multiple space-separated words.
In C2, enter =IF(TRIM(A2)="",FALSE,IFERROR(LET(words,TEXTSPLIT(TRIM(A2)," ",,TRUE),SUM(--ISNUMBER(SEARCH(words,B2)))>0),FALSE)).
Select C2 and drag its fill handle down alongside the values in columns A and B.
Check column C: TRUE means at least one word from A2 appears in B2; FALSE means none was found. For example, Roger Johnson and Johnson Motors return TRUE.

Compare Up to Two Words Using Helper Columns in Excel
Use this method when TEXTSPLIT is unavailable and each cell in column A contains no more than two space-separated words.
Find Partial Word Matches in WPS Spreadsheets
Use Text to Columns and a SEARCH formula in WPS Spreadsheets to compare up to two words from one cell with another cell.
- 1. Open the worksheet: Open your workbook in WPS Spreadsheets and place the text pairs in columns A and B.
- 2. Copy the first text column: Copy the values from A2 downward into column D. Leave columns D and E available for the split words.
- 3. Split the words: Select the copied cells in column D, choose Data > Text to Columns, and split them using a space as the delimiter.
- 4. Enter the match formula: In F2, enter =OR(AND(D2<>"",ISNUMBER(SEARCH(D2,$B2))),AND(E2<>"",ISNUMBER(SEARCH(E2,$B2)))). This checks up to two words from column A.
- 5. Fill down: Drag the fill handle from F2 down to compare the other rows. TRUE indicates a match.

Frequently Asked Questions
Does the formula match words inside longer words?
Yes. SEARCH looks for a substring, so it can find Johnson inside Johnson Motors or Johnsonville. It does not enforce whole-word boundaries.
Is the comparison case-sensitive?
No. SEARCH ignores case, so Johnson and JOHNSON match. Use FIND instead of SEARCH if you need a case-sensitive comparison.
What if my Excel version does not have TEXTSPLIT?
Use Data > Text to Columns to split the first cell into helper columns, then use SEARCH to check each word. The helper-column formula above checks up to two words.
Will the formula find a match when the first cell is blank?
No. Both methods exclude blank input from matching, so a blank first cell returns FALSE.




