How to Fix Excel Values That Look Identical but Do Not Match
Question details
Resolve lookup formula and comparison failures caused by invisible Unicode characters in spreadsheet data.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Importing data from external sources and using lookup formulas like VLOOKUP or matching functions.
- Observed behavior
- Values that appear visually identical fail to match in formulas or exact comparisons due to hidden Unicode characters like zero-width spaces.
Before attempting to remove complex Unicode characters, check if the issue is caused by standard trailing spaces by applying the TRIM function to your data.
Remove Invisible Characters using the SUBSTITUTE Formula
The most reliable method to accurately remove specific hidden Unicode characters like zero-width spaces (UNICHAR 8203) without affecting the rest of the text.
Imported data often contains zero-width spaces that normal trimming functions cannot catch. By identifying the exact Unicode character number, you can substitute it with nothing to clean the cell completely.
Click on a blank cell adjacent to the problematic data where you want the cleaned text to appear.
Type =SUBSTITUTE(B2, UNICHAR(8203), "") into the formula bar, replacing B2 with the cell reference of your problematic value. The "" indicates replacing it with nothing.
Press Enter to see the cleaned result. Click the bottom-right corner of the cell and drag it down to apply this formula to the rest of your data.
Copy the new cleaned column, right-click the original data column, and select Paste Special > Values to replace the corrupted data permanently.
Clear Hidden Characters with Find and Replace
A quick manual method useful when you can successfully highlight and copy the invisible character directly from the cell.
Clean Your Data Easily with WPS Spreadsheet
WPS Office offers a robust Spreadsheet application that fully supports advanced data cleaning functions like SUBSTITUTE, UNICHAR, and TRIM. It is an excellent, lightweight alternative that handles messy imported datasets seamlessly.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open your Spreadsheet: Launch WPS Spreadsheet and open the file containing the mismatched lookup values.
- 3. Apply Data Cleaning: Use the =SUBSTITUTE() formula or the Ctrl+H Find and Replace shortcut to strip out zero-width spaces.
- 4. Save Seamlessly: Save your cleaned dataset in the standard .xlsx format without losing any formula functionality.
Frequently Asked Questions
Why does VLOOKUP fail when values look exactly the same?
VLOOKUP and exact match comparisons look at the underlying data, not just the visual output. If imported data contains invisible formatting characters like zero-width spaces or non-breaking spaces, the system reads them as completely different text strings.
Does the TRIM function remove zero-width spaces?
No, the standard TRIM function only removes standard spaces (ASCII character 32) from the beginning and end of text. It cannot detect or remove zero-width spaces (UNICHAR 8203) or non-breaking spaces (CHAR 160).
How do I know which invisible character is causing the issue?
You can identify the exact character by using a combination of the MID and UNICODE functions. Extract the specific invisible character using MID, and wrap it in UNICODE() to get its numeric code. You can then use that number in the UNICHAR function to substitute it.
