Cleaning Messy Names and Company Data Before You Match or Merge Records

Do not overwrite your source data. Create a second column, the match key, that holds an aggressively simplified version of each name, and use that column only for finding duplicates and joining lists. The display name stays exactly as the person or company wrote it. This one habit prevents most of the harm that data cleaning does, because the key can be as blunt as you like: lowercase, no accents, no punctuation. Nobody ever sees it.

Everything below is a step in building that key, and a few steps that improve the display name itself.

Two values that look identical can differ in ways you cannot see, and a lookup treats them as strangers.

• ▸Leading, trailing and doubled spaces. Collapse every run of spaces to one and trim the ends.
• ▸Non-breaking spaces and zero width characters copied from web pages and PDFs. Replace them with ordinary spaces or delete them.
• ▸Tabs and line breaks hidden inside cells.
• ▸Unicode composition. An é can be one character or an e followed by a combining accent mark. They look the same and compare as different. Normalize to one form, usually NFC, before comparing.
• ▸Curly versus straight apostrophes in names such as O'Brien and D'Souza. Convert them all to one.

The Whitespace Remover on this site handles the first three in one pass, and the Plain Text Converter normalizes quotes and apostrophes.

For matching, lowercase the key and strip accents so that José García and JOSE GARCIA produce the same string. The Remove Accents tool turns é into e and also expands letters that are not accent combinations, such as ß into ss and ø into o.

Never strip accents from the display name. Names are part of a person's identity, and removing diacritics changes meaning in some languages. The accent-free version belongs in the key.

A column in all capitals or all lowercase looks unprofessional in mail merges, so you may want proper case for display. Blind title case is the most common way to damage a name list.

• ▸Spreadsheet PROPER functions capitalize every word and the letter after an apostrophe or hyphen. That produces O'Brien correctly, but also Mcdonald, Macdonald, Van Der Berg for a Dutch surname that should be van der Berg, and Iii for a generational suffix.
• ▸Particles such as van, von, de, del, da and ibn are often lowercase in the middle of a name. Prefixes such as Mc, Mac, O' and Fitz need rules, and Mac is not always a prefix (Macy is not Mac Y).
• ▸Suffixes such as II, III and IV should stay in capitals, and so should initials with periods.
• ▸The safest policy is to leave names as entered when they already contain mixed case, and only apply proper case to values that are entirely upper or lower. That preserves the work of people who typed their names correctly.
• ▸Always review a sample of the output. Automated name casing is a convenience and never a guarantee.

Decide one order, usually first name and last name in separate columns. Split values written as Garcia, Jose on the comma, and be aware that a comma can also separate a name from a suffix, as in Smith, Jr. Remove stray punctuation from the key, such as periods in initials and quotation marks around nicknames. Keep titles such as Dr and Mrs out of the key, since they appear on only some records.

Companies add a layer of noise that people do not have: legal forms and filler words.

• ▸Strip legal suffixes from the key: Inc, Incorporated, LLC, Ltd, Limited, Corp, Corporation, Co, GmbH, AG, SA, SAS, BV, Pty and similar. Keep a list for the countries you work with.
• ▸Replace an ampersand with the word and in both versions, or remove both, so Smith & Sons and Smith and Sons match.
• ▸Remove the word The at the start, and drop punctuation and doubled spaces.
• ▸Keep numbers. 3M and M are different companies, and so are Studio 54 and Studio.
• ▸Expect to keep a manual alias table for names that cannot be derived, such as a trading name versus a registered name, or a rebrand.

Once the key exists, sort by it and look at groups of identical keys. Extract a list of the key values, paste them into the Duplicate Line Remover with the option that shows only repeated lines, and you have the groups to review. Do not delete automatically. Two records with the same key can be different people, such as father and son at the same address, and the key cannot know.

• ▸Combine the name key with a second attribute before merging: email domain, postal code or date of birth.
• ▸Treat an exact email address match as stronger evidence than a name match, but still review shared mailboxes such as info and sales.
• ▸For near matches with spelling mistakes, fuzzy matching with an edit distance can find them, but treat its output as a list of candidates for a person to confirm, not as an automatic merge.
• ▸Keep a record of every merge: which records, which key and who approved it.

• ▸Lowercase email addresses and trim them before comparing. The domain is case insensitive, and in practice so is the local part at nearly every provider.
• ▸Reduce phone numbers to digits and a leading plus sign, and store them in the international E.164 format, which is a plus sign, the country code and the number with no spaces or punctuation, up to 15 digits in total.
• ▸Do not guess a country code when it is missing. Add it from a trusted column or flag the row.

• ▸Copy the raw data to a new sheet and never edit the original.
• ▸Clean whitespace and normalize Unicode and apostrophes.
• ▸Build the match key: lowercase, accent free, punctuation free, legal suffixes removed for companies.
• ▸Apply careful proper case to the display column only where the value is all upper or all lower case.
• ▸Group by key, review the groups and merge with a second identifier.
• ▸Record the row count at every stage, so you can explain any difference between the start and the end.