Excel Challenge 3 : Standardize Full Names

What You Will Learn

Your name list looks like this:

Raw Input	The Problems
`jOHn dOE`	Extra spaces + mixed case
`sMITH, jANE`	Mixed case + comma format
`ALICE WONDERLAND`	Leading/trailing spaces + extra middle spaces

All five names need to end up in a clean standard format: proper capitalization, no extra spaces. The formula that handles both problems at once is:

=PROPER(TRIM(A2))

Fill this down from B2 to B6. TRIM runs first — it removes extra leading, trailing, and repeated spaces. Then PROPER capitalizes each word. The order matters: if you ran PROPER first, the extra spaces would stay and the result would still look messy. TRIM first, PROPER second.

The image below shows the raw data and the expected result for each row.

PROPER(TRIM(...)) handles both spacing and capitalization in one pass.

One row contains sMITH, jANE — a last-name-first format with a comma. PROPER handles this correctly: it capitalizes "Smith" and "Jane" while keeping the comma in place. It also handles hyphenated names like "mary-ann" becoming "Mary-Ann".

The SUMPRODUCT formula in B10 compares the raw column against the cleaned column. It returns 5 if every row changed. If it returns 3, that means 2 rows were already clean — useful for measuring how messy the original data was.

The Audit Formulas

B9: =COUNTA(A2:A6) — counts total records.
B10: =SUMPRODUCT(--(A2:A6<>B2:B6)) — counts how many rows changed after cleaning. This compares each raw name against its cleaned version and counts the differences. If the number is less than 5, some rows already had correct formatting.

Going Further: Hidden Characters

If your data was copied from a website or PDF, it may contain invisible non-printable characters that TRIM does not remove. Add CLEAN to the chain:

=PROPER(TRIM(CLEAN(A2)))

CLEAN strips control characters first, then TRIM handles the spacing, and PROPER finishes with the casing.

PROPER capitalizes every word, which works well for names but can cause issues with certain words (like "McDonald" becoming "Mcdonald"). For basic name cleanup, it is good enough — for more control, you would need a more advanced approach.