Clean Messy Data in Excel With TRIM, CLEAN and PROPER

The Excel TRIM function removes extra spaces from text, CLEAN removes hidden characters, and PROPER fixes the capitals. Together with UPPER, LOWER and SUBSTITUTE, they turn a messy export into data that sorts, matches and looks right. This guide is for anyone who has pasted a list from a CRM, a web form or another system and found stray spaces and random capitals.

Why does imported data need cleaning?#

Data copied from other systems often carries problems you can't see, and they break formulas. A name with a trailing space won't match the same name without one, so VLOOKUP returns #N/A and COUNTIF misses it. Here is a typical customer export. The spaces are real:

A: Name (raw)            B: Email (raw)                     C: State   D: Phone
"  maria   gonzalez"     "Maria.Gonzalez@Harborline.COM "   or         503.555.0142
"JAMES O'NEIL "          "JONEIL@harborline.com"            Ma         312 555 0199
"priya  shah"            "  priya.shah@Harborline.com"      wa         206.555.0177
"Chen   WEI"             "Wei.Chen@HARBORLINE.com"          MA         617 555.0123

The plan is simple: build clean copies in new columns, check them, and only then replace the raw data. That way you can always compare.

How to use the Excel TRIM function#

TRIM removes spaces at the start and end of text, and squeezes any run of spaces between words down to one. The syntax has one part:

=TRIM(text)

In E2, type =TRIM(A2). The result is maria gonzalez, with one space between the words and none at either end. TRIM works in every version of Excel.

To see whether a cell has extra spaces, compare its length before and after: =LEN(A2) gives 18, and =LEN(TRIM(A2)) gives 14. If the two numbers differ, TRIM has something to remove.

Why isn't TRIM removing the spaces?#

TRIM only removes the normal space character. Text copied from web pages often contains non-breaking spaces, which look the same but are a different character (number 160). Swap them for normal spaces first, then trim:

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

CHAR(160) is the non-breaking space. SUBSTITUTE replaces each one with a normal space, and TRIM tidies the result.

How to remove hidden characters with CLEAN#

CLEAN removes non-printing characters: control codes that don't show up as letters, such as line breaks inside a cell. They often arrive in data exported from other programs. The syntax:

=CLEAN(text)

CLEAN removes character codes 0 to 31, but not spaces, so it pairs well with TRIM:

=TRIM(CLEAN(A2))

Use this when cells look fine but still won't match, or when text wraps onto two lines for no visible reason.

How to fix capitals with PROPER, UPPER and LOWER#

These three change capital letters, and each takes one part, the text:

PROPER also capitalizes the letter after an apostrophe or a hyphen, so O'Neil and El-Sayed come out right. But it doesn't know name rules: mcdonald becomes Mcdonald, and a brand like iPhone becomes Iphone. Always check cleaned names by eye.

How to combine TRIM, PROPER and LOWER in one formula#

Put one function inside another to fix several problems at once. This is called nesting, and Excel works out the inside function first. For the names:

=PROPER(TRIM(A2))

TRIM removes the spaces, then PROPER fixes the capitals, so " maria gonzalez" becomes Maria Gonzalez and "JAMES O'NEIL " becomes James O'Neil. For the emails, use LOWER instead:

=LOWER(TRIM(B2))

This gives maria.gonzalez@harborline.com, with the trailing space gone. For the states, =UPPER(C2) gives OR, MA, WA and MA. Fill each formula down (Windows: Ctrl+D; Mac: ⌘D).

How to fix separators with SUBSTITUTE#

SUBSTITUTE swaps one piece of text for another: =SUBSTITUTE(text, old_text, new_text). The phone numbers mix dots and spaces. To use dashes everywhere, replace the dots, then put that inside a second SUBSTITUTE that replaces the spaces:

=SUBSTITUTE(SUBSTITUTE(D2,".","-")," ","-")

All four numbers come out in the same style: 503-555-0142, 312-555-0199, 206-555-0177 and 617-555-0123. SUBSTITUTE is also how you delete a character: replace it with empty text, as in =SUBSTITUTE(D2,".","").

For a one-off fix, Find and Replace does the same job without a formula (Windows: Ctrl+H; Mac: ⌃H). It changes the cells directly, so make a copy of the data first.

How to find the cells that still need cleaning#

Before and after cleaning, it helps to know how many cells have problems. A wildcard in COUNTIF finds trailing spaces: "* " means any text that ends with a space.

=COUNTIF(A2:A5,"* ")

On the raw names this returns 1 (JAMES O'NEIL has a trailing space). Use " *" for leading spaces.

To flag every cell that cleaning would change, compare the raw value with the cleaned one using EXACT, which is case-sensitive:

=EXACT(A2,PROPER(TRIM(A2)))

FALSE means the cell needs cleaning; TRUE means it's already tidy. Filter that column for FALSE to review the rows one by one.

How to replace the raw data with the clean version#

Once the clean columns look right, turn them into plain values so they no longer depend on the raw data:

1. Select the clean column and copy it (Windows: Ctrl+C; Mac: ⌘C).
2. Select the first cell of the raw column.
3. Open Paste Special (Windows: Ctrl+Alt+V; Mac: ⌃⌘V), choose Values, and confirm.
4. Delete the helper column.

Two more checks are worth doing. Numbers that came in as text, such as "1250" sitting on the left of the cell, need =VALUE(TRIM(A2)) to become real numbers. And if you need to split cleaned names into first and last, see how to split first and last names. Pulling a company out of an email is a job for TEXTBEFORE and TEXTAFTER, and formatting numbers inside text is covered in the TEXT function guide.

The TRIM function guide is a quick syntax reference.

Practise it#

Practise cleaning messy text in a live spreadsheet: trim names, fix capitals with PROPER, UPPER and LOWER, and standardize phone numbers with SUBSTITUTE. Then take the text skill summary, where you clean a whole contact list and build a mailing line from it.