Clean messy names in Excel with TRIM, CLEAN, and PROPER

Names pasted from another system arrive dirty: leading spaces, doubled spaces, all caps, a line break inside a name, a space that is not really a space. Three functions clean them — but the short formula misses two of the seven names in this tutorial, and the misses are invisible until you test for them.

1What you need

• Excel, Google Sheets, or LibreOffice. TRIM, CLEAN, PROPER, and SUBSTITUTE work in all three; the version note in step 8 matters for one function name.
• The file above, or a blank workbook if you want to type the formulas in yourself.

2The messy names

Column A holds seven names as imported. Three have extra spaces, one is all caps, one is lowercase, one has a line break where the space should be, and one has a non-breaking space between the first and last name:

3What each function does

TRIM removes all spaces from text except for single spaces between words (TRIM function — Microsoft Support). CLEAN removes the first 32 nonprinting characters from text. PROPER capitalizes the first letter in each word and lowercases the rest. Microsoft's own cleaning guidance recommends using the three together with SUBSTITUTE for the characters TRIM and CLEAN were not designed for (Top ten ways to clean your data — Microsoft Support).

4The basic formula

Nest the three functions, innermost first — CLEAN, then TRIM, then PROPER:

B3  =PROPER(TRIM(CLEAN(A3)))

It handles five of the seven names exactly: “Maria Garcia”, “John Smith”, “Anne-Marie O'Brien”, “Chen Wei” and “José Martínez”. Note the apostrophe: PROPER capitalizes the letter after it, so O'BRIEN becomes O'Brien, not O'brien. Accented letters are treated as letters, so JOSÉ becomes José.

5The line-break trap

The basic formula turns “sara [line break] lee” into “Saralee”. CLEAN removes the line break — it is a nonprinting character — and nothing puts a space in its place, so the two words join. The length column in the file shows it: 7 letters instead of 8. The fix is to swap the line break for a space before CLEAN runs, with SUBSTITUTE and CHAR(10):

C6  =PROPER(TRIM(CLEAN(SUBSTITUTE(A6, CHAR(10), " "))))

Result: “Sara Lee”. Any character CLEAN deletes can join words this way, so replace the ones that stand for a space before you clean.

6The non-breaking space trap

The basic formula returns “Marie Curie” for the last name — and it looks right. It is not: the space between the names is still a non-breaking space (character 160). TRIM was designed for the ordinary space (character 32) and does not remove the non-breaking space; Microsoft states this directly on the TRIM page. The two cells look identical, but the file's EXACT column says FALSE for that row, and both lengths read 11 — the difference is the character, not the count.

The fix is the same SUBSTITUTE pattern, this time for character 160:

C9  =PROPER(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A9, CHAR(10), " "), CHAR(160), " "))))

Result: “Marie Curie” with an ordinary space. The EXACT column still reads FALSE for this row — correctly, because the basic formula kept the non-breaking space and the full formula replaced it. The two formulas differ on exactly the two rows that needed the extra step.

7The full formula, and the check column

Use the full formula from step 6 for every row, not just the two problem rows — it returns the same result as the basic formula on the other five, so one formula covers the whole list. Then add a check column so a future paste that introduces a new kind of dirt shows up instead of slipping through:

D3  =EXACT(B3, C3)

TRUE means the two formulas agree; FALSE means the full formula changed something the basic one missed. In the checked file, five rows read TRUE and two read FALSE. The LEN columns beside it catch the other kind of difference — a result that is too short because two words joined.

8Paste as values, and a version note

When the names are clean, copy the result column and paste it back as values over the originals, then delete the formula columns. Microsoft's cleaning guidance lists exactly these steps: add the formula in a new column, fill it down, copy it, paste as values, and remove the original column.

One version note: the file uses CHAR(10) and CHAR(160). Excel also has UNICHAR, which does the same job — but in the recalculation for this tutorial, LibreOffice 24.2.7.2 returned #NAME? for UNICHAR, so the shipped file uses CHAR, which works in Excel, Google Sheets and LibreOffice alike.

Traps

How this was checked

The workbook was built with openpyxl 3.1.2 (formulas only, no typed-in results), recalculated by LibreOffice 24.2.7.2 in headless mode, and read back with openpyxl in data-only mode. The expected values were computed independently in Python 3.12.3 with matching TRIM, CLEAN, and PROPER implementations: all seven basic results, all seven full results, the EXACT column, and both length columns matched exactly. UNICHAR was tested separately in LibreOffice 24.2.7.2 and returned #NAME?, which is why the file uses CHAR.

Sources

• TRIM function — Microsoft Support — what TRIM removes, and the note that it does not remove the non-breaking space (character 160) on its own.
• Top ten ways to clean your data — Microsoft Support — the TRIM, CLEAN, and SUBSTITUTE combination, the PROPER and CLEAN definitions, and the paste-as-values steps.