How to merge duplicate contacts in Excel when names are spelled differently (2026)

Updated 10 October 2026 · 4 min read

Excel's Data > Remove Duplicates only deletes rows whose selected cells are identical, and Microsoft warns that "the duplicate data is permanently deleted". To merge contacts whose names are reversed (DUPONT Jean), misspelled (Lefebvre / Lefèvre) or initialled (J.P. Dupont), you need three things Excel does not do in one step: normalise the values, match names approximately but only when another field agrees (same ZIP code, city or company), and combine the rows instead of deleting them so that a phone number present on one copy and an address present on the other both survive.

Why Remove Duplicates misses most real duplicates

The last line is the trap in the other direction: selecting only the name column merges different people. Microsoft's own page suggests highlighting duplicates first with Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values and copying the data to another sheet before removing anything (Find and remove duplicates).

Method in Excel, with helper columns

1. Copy the sheet. Work on a copy; keep the original row number in a column so you can trace every merge.
2. Email key: =LOWER(TRIM(C2)).
3. Phone key: strip spaces, dots, dashes and brackets with nested SUBSTITUTE, then replace a leading 0 with the country code. Watch for numbers Excel already damaged: it "automatically removes leading zeros" and keeps only 15 significant digits (Keeping leading zeros and large numbers), so 0612345678 may already be 612345678.
4. Name key (order-free): in Excel for Microsoft 365 or Excel 2024, =TEXTJOIN(" ",TRUE,SORT(TEXTSPLIT(LOWER(SUBSTITUTE(A2,"-"," "))," ",,TRUE),,,TRUE)) turns DUPONT Jean-Pierre and Jean Pierre Dupont into the same dupont jean pierre. TEXTSPLIT is not available in older versions.
5. Flag matches: =COUNTIF(E:E,E2)>1 on each key, and a combined key such as name key & ZIP code.
6. Sort by key, then merge by hand: for each group, keep the most complete row and copy missing cells from the others.

This catches case, spacing, word order and phone formatting. It does not catch spelling (Lefebvre / Lefèvre) or initials (J.P.), because Excel worksheet formulas have no built-in fuzzy comparison.

Options for the spelling part

Power Query's fuzzy matching uses the Jaccard similarity and a threshold from 0 to 1 with 0.80 by default (Create a fuzzy match). The Fuzzy Lookup add-in's download page lists Windows and Excel 2007, 2010, 2013 or 2016 as prerequisites.

Rules that keep false merges out

• Never merge on a similar name alone. Require the same ZIP code (or the same city when ZIP is empty) or the same company.
• Treat a shared phone with different first names as two people. Households share landlines; companies share switchboards.
• Do not let an initial chain two names. J. Martin may match Julie Martin or Jacques Martin, but Julie and Jacques must not end up in one group.
• Watch masculine/feminine pairs. Jean / Jeanne, Michel / Michelle are close by spelling and are different people.
• Keep a merge log with the original row numbers of each group and the row kept.

FAQ

Which row should I keep? The most complete one, or the most recently updated if the file has a date column. Then fill its empty cells from the other rows rather than discarding them.

How similar is "similar"? With Jaro-Winkler on individual words, lefebvre / lefevre scores 0.975 and Jon / John about 0.93, while Jean / Marie is under 0.5. A threshold around 0.90 on the whole name, combined with a same-ZIP or same-company condition, is a reasonable start.

Does word order matter? It should not. Compare names as a set of words so that DUPONT Jean equals Jean Dupont.

Can I undo Remove Duplicates? Only with Undo right away. Microsoft states the duplicate data is permanently deleted, so work on a copy.