How to Standardize Names in Excel: Proper Case and Exceptions

A mailing list with names in three styles looks careless and breaks matching: "JOHN SMITH", "john smith" and "John Smith" are the same person but can be three different values to a CRM, a lookup or a duplicate check. Standardising names is easy for the common cases and risky for the unusual ones. This guide shows both.

Step 1: remove the spaces first

Names pasted from forms and websites often carry extra spaces. Clean those before changing case:

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

For more on stray spaces, see removing extra spaces in Excel.

Step 2: change the case with PROPER

=PROPER(TRIM(A2))

PROPER capitalises the first letter of every word and lowercases the rest. It handles hyphens and apostrophes well: mary-jane o'brien becomes Mary-Jane O'Brien.

What PROPER gets wrong

Capitalisation rules for names differ by language and by family. There is no formula that is right for every name, so protect the cells that are already correct.

Step 3: only change cells that need it

Apply PROPER only when the cell is entirely upper case or entirely lower case. A name in mixed case was most likely typed by a person who knows how it is spelt:

=IF(OR(EXACT(A2,UPPER(A2)),EXACT(A2,LOWER(A2))),PROPER(TRIM(A2)),TRIM(A2))

EXACT compares with case, which a plain = does not.

Step 4: fix the known exceptions

After PROPER, repair the patterns you actually have in your data with SUBSTITUTE:

=SUBSTITUTE(SUBSTITUTE(B2," Iii"," III")," Ii"," II")

For Mc names, which are consistent enough to fix in bulk, change the letter after "Mc" back to a capital by hand or with Find and Replace on your own list. Do not try to cover Mac names with a rule: Macdonald and Macy are both right.

Step 5: "Last, First" into "First Last"

=TRIM(MID(A2,FIND(",",A2)+1,100))&" "&TRIM(LEFT(A2,FIND(",",A2)-1))

Wrap it in IFERROR(...,A2) so rows without a comma stay as they are. Run it only on cells that contain exactly one comma, because "Smith, John, Jr." is not the same pattern.

Step 6: titles and suffixes

Remove "Mr", "Mrs", "Dr" and similar only if you are sure you do not need them, since they carry information. If you only need the first and last name, see splitting a full name.

A whole file at once

DecodingPro fixes names that are entirely upper or lower case, keeps particles such as van and de in lower case, writes suffixes such as II and III in capitals, repairs names like McDONALD, and leaves any name that is already in mixed case exactly as you typed it. Every change is listed.

Questions people ask