Build a Normalized Match Key

Excel Formulas › Data Cleaning

All versionsTRIM

Records that should match often don’t — "Acme Inc." vs "ACME INC". Build a normalized key — trimmed, cleaned, uppercased, punctuation removed — so lookups and dedup compare apples to apples.

Quick formula: a clean key for matching:
=UPPER(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2, CHAR(160), " "), ".", ""))))
Replace non-breaking spaces, strip control chars and periods, collapse spaces, and uppercase — a canonical key.

Functions used (tap for the full reference guide):

TRIMUPPER

The example

"Acme Inc." and "ACME INC" → ACME INC.

AB
1RawKey
2Acme Inc.ACME INC

The formula

The formula:

=UPPER(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2, CHAR(160), " "), ".", "")))) // canonical comparison key

How it works

How it works:

  1. Swap non-breaking spaces (CHAR 160) for normal ones, then CLEAN removes control characters.
  2. Strip punctuation like periods and commas with nested SUBSTITUTE.
  3. TRIM collapses extra spaces; UPPER removes case differences.
  4. Compute the key on both lists and match on it — not the raw text.

Normalize both sides, then VLOOKUP the key. Add a key column to each table with the same formula, and join on the key instead of the messy original. This single technique fixes the bulk of “why won’t my lookup match” problems — case, stray spaces, hidden CHAR(160), and trailing punctuation all vanish into one canonical string.

Try it: interactive demo

Live demo

Two values that should match.

Keys match?

Variations

Remove all spaces

Strictest key:

=UPPER(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),"")," ",""))

Strip more punctuation

Commas too:

=SUBSTITUTE(SUBSTITUTE(key, ",", ""), "-", "")

Match on the key

Lookup:

=VLOOKUP(key, other_keys, 2, FALSE)

Pitfalls & errors

Same key both sides. Apply the identical formula to both lists before matching.

Don’t over-strip. Removing too much can merge distinct records — balance it.

Keep the original. The key is for matching; display the raw value.

Practice workbook

📊
Download the free Build a Normalized Match Key practice workbook
A match-key sheet with the no-spaces, more-punctuation, and lookup variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I build a match key in Excel?
Normalize fully: =UPPER(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2, CHAR(160), " "), ".", "")))). Match on this key, not the raw text.
Why won't my VLOOKUP match identical-looking text?
Case, extra spaces, hidden CHAR(160), or punctuation differ. A normalized key on both sides fixes it.
Should I remove all spaces?
For the strictest matching, yes — but it can merge distinct names, so balance how aggressively you normalize.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Remove non-breaking spaces · Clean text · Standardize yes/no

Function references: TRIMUPPER