How to fix mismatched IDs before joining two spreadsheets

Typical ID mismatches

Each of these is the same customer, and none will match another in a lookup or a join. The fix isn't to make one file look like the other by hand; it's to derive a canonical key — here the number 42 — from every format, in a repeatable step.

Start by listing the variations. Sort each ID column and look at the top, the bottom and a sample in the middle; check the lengths with =LEN(A2) and filter for the unusual ones. Note every prefix, separator and padding width you see.

Choose the canonical form

• Number-only (42): simplest when IDs are numeric underneath. Strip everything that isn't a digit, convert to a number.
• Padded text (00042): use when leading zeros are meaningful or IDs are compared as text elsewhere.
• Full code (CUST-0042): use when different prefixes mean different things — CUST-42 and SUPP-42 are different records, so stripping the prefix would merge them.

That last point is the main risk in ID normalisation. Before stripping a prefix, check that each file uses only one prefix in that column.

Excel formulas

For the four formats above, in Microsoft 365 or Excel 2021 a normalised numeric key is:

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(TRIM(A2)), "CUST", ""), "-", ""), " ", ""))

Read it from the inside out: trim, upper-case, remove the prefix, remove dashes and spaces, convert to a number (which drops leading zeros). If you need padded text instead, wrap the result: =TEXT(…, "00000").

In Microsoft 365, REGEXREPLACE is rolling out and makes this shorter — =VALUE(REGEXREPLACE(A2, "[^0-9]", "")) keeps only digits — but check it exists in your version first. Without it, LET can strip non-digits with a MID array:

=LET(t, A2, c, MID(t, SEQUENCE(LEN(t)), 1), VALUE(CONCAT(IF(ISNUMBER(--c), c, ""))))

Add the key column to both sheets, then do the lookup or join on it. Google Sheets has REGEXREPLACE built in: =VALUE(REGEXREPLACE(TRIM(A2), "[^0-9]", "")).

Power Query

In each query, add a custom column (Add Column → Custom Column) that builds the key, then merge on it:

= Table.AddColumn(Source, "Key", each
    Number.From(Text.Select(Text.From([CustomerID]), {"0".."9"})), Int64.Type)

Text.Select keeps only the listed characters, so prefixes, dashes and spaces disappear. If you need to keep a prefix, use Text.Upper and Text.Replace steps instead. Because the step is saved, next month's file is normalised the same way. Remember that Power Query merges are case-sensitive and type-sensitive: both key columns must be the same type.

SQL

WITH billing AS (
  SELECT *, CAST(regexp_replace(customer_id, '[^0-9]', '', 'g') AS INTEGER) AS key
  FROM billing_raw
),
crm AS (
  SELECT *, CAST(regexp_replace(id, '[^0-9]', '', 'g') AS INTEGER) AS key
  FROM crm_raw
)
SELECT b.customer_id, c.id AS crm_id, c.owner, b.balance
FROM billing b
LEFT JOIN crm c USING (key);

That's DuckDB syntax — the 'g' flag replaces every match, not just the first; PostgreSQL is the same. In pandas, df['key'] = df['id'].astype(str).str.replace(r'\D', '', regex=True).astype(int) does the same job. Analistable loads both files into DuckDB in the browser, so you can ask “join billing to CRM on the digits in the customer ID” and check the regexp_replace in the SQL it writes.

Common patterns and their fixes

Apply the same steps, in the same order, to both files. Applying a rule to one side only is the commonest reason a normalised join still fails. Hidden spaces deserve their own check — see trailing spaces break lookups — as do numbers stored as text.

Check the key before you join

1. Unparseable values: filter the key column for errors or blanks. An ID with no digits, or a different scheme altogether, needs handling by hand.
2. Collisions: count each key with =COUNTIF(B:B, B2). If CUST-0042 and SUPP-0042 both became 42, the join will fan out — see duplicate rows after joining.
3. Match rate: count how many keys from file A are found in file B before and after normalising.

Of the 29 still unmatched, look at a sample: some will be customers missing from the CRM, others a format you didn't anticipate. Iterate until the remaining misses are genuine. For the join itself, see joining two spreadsheets on a common column and the join spreadsheets guide; for IDs that differ by typos rather than format, Power Query fuzzy merge.

Frequently asked questions

How do I join when IDs have different formats?

Build a normalised key column in both files — same case, no prefix or separators, consistent padding — and join on that instead of the raw IDs.

Should I store the cleaned ID over the original?

No. Keep the original for traceability and add the cleaned key as a new column.

How do I add leading zeros to match a padded ID?

Use =TEXT(A2, "00000") in Excel or lpad(CAST(id AS VARCHAR), 5, '0') in SQL, with the width of the padded format.

What if the IDs don't share any common part?

You need a mapping table that lists each pair of IDs, or a different shared field such as email. Normalising can't create a link that isn't in the data.

Related guides

• How to join two spreadsheets on a common column
• How to use fuzzy merge in Power Query
• How to do an inner join in Excel
• How to join on dates and date ranges
• How to join two lists on email addresses safely