TRIM and CLEAN: why "identical" values refuse to match

You can see that two cells say the same thing. Excel disagrees, and your lookup returns "Not found." Nine times out of ten the culprit is invisible: a trailing space or a non-printing character riding along in the data. TRIM and CLEAN strip both.

The formula

=TRIM(CLEAN(A2))

TRIM removes leading, trailing, and repeated spaces (it leaves single spaces between words).
CLEAN removes non-printing characters — line breaks, tabs, and control codes that sneak in from PDFs, web pages, and system exports.

Run it on both sides of any comparison before you compare, then use the trimmed columns for your lookup.

Why this matters more than it sounds

Trailing spaces are the quiet killer of reconciliations and lookups. "North" and "North " look identical in the cell, line up perfectly to the eye, and will never match in an XLOOKUP or a COUNTIF. Because you can't see the problem, you lose time hunting a data error that isn't there — the data's fine, it's just dirty.

Make it permanent

TRIM/CLEAN give you a formula column. When you're done, convert it to real values — copy, then Paste Special → Values (Ctrl+Alt+V, then V) — so you can delete the original messy column and sort or send the clean one.

One thing TRIM won't catch

Non-breaking spaces (character 160), common in data copied from the web, survive TRIM. If a value still won't match after TRIM/CLEAN, nuke them with a SUBSTITUTE first:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

Dirty values are the first thing to rule out when a match fails. The complete, both-directions method is in how to compare two lists in Excel — and reconciling across entities every month is what TrueBalance automates.