How do I match two lists when one has extra spaces in the ID values?
Quick answer
Keep the original IDs unchanged. In a helper column for each list, apply the same cleanup—such as Excel’s TRIM for ordinary spaces—then compare the helper values instead of the originals. Review unmatched rows and check whether cleanup made two different original IDs identical. TRIM does not remove every possible whitespace character, so persistent mismatches may need closer inspection. [S1]
The question, the details, the next step.
Why do two IDs that look identical fail to match?
A cell can contain a leading space before the ID, a trailing space after it, or repeated spaces within it. Those characters may be hard to notice, but they can make values differ even when the IDs look alike on screen. Nonprinting characters can also cause unexpected results in searches. [S1]
For example, AB-204 and AB-204 may look the same at a glance, while the second value has a trailing space. Keep the original columns intact so you can check any unexpected result against the values you started with.
What’s a simple way to clean both lists for comparison?
Add a helper column beside each original ID column and use the same cleanup on both. In Excel, if an ID is in A2, enter =TRIM(A2) in a helper cell and fill it down; repeat for the other list using its ID column. TRIM removes ordinary ASCII spaces, including leading, trailing, and repeated spaces. [S1]
Compare the helper values, rather than comparing one cleaned column against an untouched column. For example, if a helper value in the first list is AB-204 and the corresponding value in the second list’s helper column is also AB-204, treat that as a possible match to review—not proof that the records are the same.
I used TRIM, but some IDs still don’t match. What should I check?
TRIM is limited to ordinary ASCII spaces; it does not cover every possible whitespace character. [S1] Inspect the original cells and the cleaned helper values side by side. Look for differences that the cleanup did not address rather than repeatedly changing IDs by eye.
CLEAN and SUBSTITUTE can help with certain nonprinting or other unwanted characters, but the appropriate cleanup depends on what is actually present. [S1] Avoid applying a broad replacement blindly: a character that looks unusual might be part of a legitimate identifier. If you cannot determine the intended value, preserve the row as an exception and ask whoever owns the ID data to confirm it.
How do I verify that cleanup hasn’t created incorrect matches?
Review results in both directions: find IDs in the first cleaned list with no match in the second, and IDs in the second with no match in the first. For each exception, compare the original values with the helper values. Also check for collisions—cases where different original IDs become the same cleaned value. Normalize both columns consistently, preserve the originals, and inspect exceptions. [S1]
A review layout can help you compare the values:
Original A | Cleaned A | Original B | Cleaned B | Review note
Example: AB-204 | AB-204 | AB-204 | AB-204 | Check originals; cleaned values match.
This is a possible match, not proof that the records represent the same thing. Confirm the result against your ID rules before using it for updates or other consequential work. TRIM does not handle every possible whitespace character, and a cleaned match is not automatically a valid match. [S1]
A practical checklist
Keep unchanged copies of both original ID columns.
Create helper values for both lists using the same cleanup method.
Compare cleaned helper values, not cleaned IDs against raw IDs.
Review unmatched rows by checking originals and helper values side by side.
Check whether different original IDs collapse to the same cleaned value.
Investigate persistent mismatches; TRIM does not remove every possible whitespace character. [S1]
A few more useful answers
Should I replace the original IDs with the cleaned values?
Not while validating the match. Preserve the originals and use helper values so you can investigate exceptions or collisions.
Does TRIM remove every invisible character?
No. TRIM removes ordinary ASCII spaces, but it does not cover every possible whitespace character. Inspect persistent mismatches before choosing another cleanup. [S1]
Can two different IDs become identical after cleanup?
That is a possibility to check for. Compare original IDs wherever cleaned values repeat, and confirm the match under your ID rules.
Sources
References used for this guide.
- [S1] Top ten ways to clean your data — Microsoft Support support.microsoft.com · Checked