Function Problems

Find a Partial Word Match Between Two Cells in Excel

Chanuka GeekiyanageChanuka Geekiyanage Nov 13, 2026 869 views

Question details

Determine whether any word from one cell appears in another cell without using Kutools.

Find a Partial Word Match Between Two Cells in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the comparison formula

In C2, enter =IF(TRIM(A2)="",FALSE,IFERROR(LET(words,TEXTSPLIT(TRIM(A2)," ",,TRUE),SUM(--ISNUMBER(SEARCH(words,B2)))>0),FALSE)).

2
Fill the formula down

Select C2 and drag its fill handle down alongside the values in columns A and B.

3
Review the results

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.

Use TEXTSPLIT and SEARCH to Check Each Word in Excel
How SEARCH matches text: SEARCH is case-insensitive and finds substrings, so a word can match within a longer word. It also treats question marks and asterisks as wildcards.
Compare text in WPS Spreadsheets

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. 1. Open the worksheet: Open your workbook in WPS Spreadsheets and place the text pairs in columns A and B.
  2. 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. 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. 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. 5. Fill down: Drag the fill handle from F2 down to compare the other rows. TRUE indicates a match.
Find shared words without installing a separate add-inOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and toolsWork with a free, easy-to-use office suite
microsoft office alternative - wps office

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.