How to Find the Difference Between Two Text Cells in WPS Office Excel
Question details
The user wants to extract text that appears in one Excel-compatible spreadsheet cell but not another, using either a simple formula or an Excel 365 dynamic-array solution.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is comparing two text cells and wants to isolate the text difference from one cell.
- Observed behavior
- The user needs a formula or dynamic-array method to return the text difference between two cells.
Check that both cells contain text or text strings and confirm the comparison direction: the formula subtracts the text in the second cell from the first. If you use the dynamic-array method, make sure your WPS Office version supports LAMBDA and TEXTSPLIT.
Use TRIM and SUBSTITUTE to extract the text difference from one cell
Use this for a simple one-to-one cell comparison when you want to remove the exact content of one cell from another.
The formula removes the second cell's text from the first cell and trims extra spaces. It is the fastest method for basic text differences, but it is case-sensitive and depends on the exact wording and order of the text.
Click the cell where you want the difference to appear, for example C2.
Type =TRIM(SUBSTITUTE(A2,B2,"")) in the formula bar or directly into C2.
Press Enter. WPS Office Spreadsheet removes the text in B2 from A2 and displays the remaining text.
Remove multiple space-separated substrings with an Excel 365 dynamic-array LAMBDA formula
Use this when you have lists of main and secondary text values and need to remove several substrings at once with an Excel 365-style dynamic array.
Use WPS Office Spreadsheets to compare text cells with Excel-compatible formulas
WPS Office Spreadsheets supports the same TRIM, SUBSTITUTE, LAMBDA and dynamic array formulas, so you can extract text differences without switching tools.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and create or open a workbook containing the text cells to compare.
- 2. Enter the comparison formula: Click the result cell, type =TRIM(SUBSTITUTE(A2,B2,"")) and press Enter.
- 3. Use Name Manager for dynamic arrays: If you need the LAMBDA method, open Formulas > Name Manager and add the Removeλ and SUBSTλ definitions.
- 4. Save in Excel format: Click Menu > Save As and choose .xlsx to keep compatibility with Microsoft Excel.
Frequently Asked Questions
Why does the formula return a blank cell?
If the text in the second cell appears exactly in the first cell and in the same order, SUBSTITUTE removes all of it, leaving no visible text. The comparison direction matters: =TRIM(SUBSTITUTE(A2,B2,"")) subtracts B2 from A2. Reverse the cell references to subtract A2 from B2.
Is the TRIM and SUBSTITUTE formula case-sensitive?
Yes, SUBSTITUTE is case-sensitive. For a case-insensitive comparison, you can first convert both texts to the same case with UPPER, LOWER, or use a different matching approach.
Can I compare more than two text cells?
For simple cases, you can nest SUBSTITUTE formulas. For lists of values, use the MAP and LAMBDA method described above with main and secondary ranges or arrays.
Does this work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports TRIM and SUBSTITUTE, and recent versions also support LAMBDA, MAP, REDUCE and TEXTSPLIT for dynamic array formulas.
What if the text order is different between cells?
These formulas remove matching substrings, so if the wording order differs, the result may not be the expected conceptual difference. In that case, split the texts and compare word by word, or use a macro/script for advanced comparison.
