Function Problems

How to Compare Two Excel Text Cells and Return Different Words

Partner Editor Oct 12, 2026 871 views

Question details

The user wants to compare the text content of two cells and extract the specific words that differ between them into a single adjacent cell.

Compare Two Excel Text Cells and Return Different Words

Product: Excel
Device & OS: not provided
Scenario: Comparing textual data in two distinct cells to spot discrepancies, missing items, or unique words.
Observed behavior: Needs to identify and display only the differing words by utilizing modern dynamic array formulas.

Before you start

Ensure you are using a modern version of your spreadsheet software (such as Microsoft 365, Excel 2021, or the latest WPS Office) that supports dynamic array functions like TEXTSPLIT, VSTACK, and LET.

Solution 1Recommended

Use Dynamic Array Formulas to Find Different Words

Combine TEXTSPLIT, FILTER, XMATCH, VSTACK, and TEXTJOIN to extract unmatched words between two cells efficiently.

By breaking down the text in each cell into an array of individual words, you can cross-check them against each other. The unmatched words from both arrays are then stacked and joined back together into a single text string.

Select the target cell

Click on the empty adjacent cell where you want the differing words to be displayed.

Enter the formula

Input the following formula: =LET(a,TEXTSPLIT(A1," "),b,TEXTSPLIT(B1," "),d,VSTACK(FILTER(a,ISNA(XMATCH(a,b))),FILTER(b,ISNA(XMATCH(b,a)))),TEXTJOIN(" ",TRUE,UNIQUE(d))). Replace A1 and B1 with your actual cell references.

Adjust delimiters if necessary

If your text contains punctuation marks like commas or line breaks, modify the TEXTSPLIT delimiter argument from a simple space (" ") to an array of delimiters, such as {" ", ",", "."}.

Execute the formula

Press Enter to run the formula. The cell will now display only the words that appear in one cell but not the other.

Use Dynamic Array Formulas to Find Different Words

Formula Breakdown: TEXTSPLIT separates the words, XMATCH checks for matches, FILTER keeps only the non-matches (where ISNA is true), VSTACK merges the results from both directions, and TEXTJOIN combines them back with a space delimiter.

Data Comparison in WPS

Easily Compare Text Cells in WPS Spreadsheet

WPS Spreadsheet supports a comprehensive list of advanced dynamic array formulas, allowing you to easily handle complex data comparisons. Follow these steps to extract different words between cells using WPS.

1. Open your spreadsheet: Launch WPS Office and open your workbook containing the text data.
2. Apply the comparison formula: Select the destination cell and paste the LET formula containing TEXTSPLIT and TEXTJOIN.
3. Get the result: Press Enter to instantly see the differing words extracted into a single cell.

Fully compatible with Microsoft Excel formulas, including TEXTSPLIT and TEXTJOIN.Supports dynamic array formulas for advanced data processing and text manipulation.Free and lightweight alternative for seamless productivity.

Frequently Asked Questions

What if my cells contain punctuation marks?

You can modify the TEXTSPLIT function in the formula to handle multiple delimiters. For example, instead of a single space, use an array constant like {" ", ",", ".", "!"} to split the text by spaces and punctuation.

Why is my formula returning a #NAME? error?

The #NAME? error usually occurs if your version of Excel or spreadsheet software does not support modern dynamic array functions like TEXTSPLIT or VSTACK. Ensure you are using Microsoft 365, Excel 2021, or the latest version of WPS Office.

Can I compare cells without case sensitivity?

Yes, the XMATCH function handles case-insensitive matches by default. However, if your text has inconsistent capitalization, you can wrap your cell references in the LOWER() function within the formula to normalize the text before comparing.

Is there a way to highlight differences instead of extracting them?

To highlight differences, you would typically use Conditional Formatting. However, comparing word-by-word within a single cell's string for highlighting requires VBA macros, as standard Conditional Formatting applies to the entire cell rather than specific words.

Excel & Data Problems