Clean messy names with TRIM, CLEAN, and PROPER

How it works, step by step

1. 01CLEAN(A2) removes non-printing characters with codes 0 to 31, including tabs and the line breaks that come from multi-line CRM fields.
2. 02TRIM removes spaces at the start and end of the text and reduces runs of spaces between words to a single space.
3. 03PROPER capitalizes the first letter of each word and lowercases the rest, so jOHN smith becomes John Smith.
4. 04Once the results look right, copy column B and paste it over column A as values (Paste Special > Values).

The same logic in four tools

=PROPER(TRIM(CLEAN(A2)))

=PROPER(TRIM(CLEAN(A2)))

SELECT initcap(trim(regexp_replace(name, '\s+', ' ', 'g'))) AS clean_name
FROM contacts;

import pandas as pd

contacts["clean_name"] = (
    contacts["name"]
    .str.replace(r"\s+", " ", regex=True)
    .str.strip()
    .str.title()
)

Mistakes that break it

• !TRIM does not remove the non-breaking space (CHAR(160)) that comes with text copied from web pages. Replace it first: =PROPER(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))).
• !PROPER capitalizes any letter that follows a non-letter. o'neil becomes O'Neil, which is correct, but it's becomes It'S and mcdonald becomes Mcdonald. Python's str.title() behaves the same way.
• !PROPER lowercases acronyms, so IBM becomes Ibm and USA becomes Usa. Skip PROPER on company names, or fix known exceptions with SUBSTITUTE afterward.
• !CLEAN deletes line breaks without inserting a space, so a name split across two lines becomes JohnSmith. Use SUBSTITUTE(A2,CHAR(10)," ") before CLEAN when line breaks separate words.

Common questions

Why doesn't TRIM remove spaces in Excel?

The spaces are probably non-breaking spaces (CHAR(160)) from a web page or PDF, which TRIM leaves alone. Replace them with SUBSTITUTE(A2,CHAR(160)," ") and then apply TRIM.

What is the difference between TRIM and CLEAN?

TRIM removes extra space characters. CLEAN removes non-printing control characters such as line breaks and tabs. Text from exports usually needs both.

What is the pandas equivalent of PROPER?

Series.str.title(). Combine it with .str.replace(r"\s+", " ", regex=True) and .str.strip() to match PROPER(TRIM(CLEAN())).