Fix Messy Names in Google Sheets with One Formula (TRIM + PROPER)
Clean extra spaces and inconsistent capitalization in imported names.
Download practice data · CSVStart the exerciseWatch the tutorial
Watch on YouTube · ShortTry it yourself
Import at A1. Enter the formula in C2 and fill to C9. Emma Wilson is the first cleaned result. Check names manually: PROPER may change intentional capitalization, and TRIM does not remove every kind of whitespace.
=PROPER(TRIM(A2))Detailed steps
- 1. Add a clean output column
Keep the imported names in column A. Add a Clean name header in column C, and select C two. A separate output makes it easy to compare before and after.
- 2. Remove extra ordinary spaces
TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to one. Start with TRIM, open a parenthesis, A two, and close the parenthesis.
- 3. Fix the capitalization
Now wrap that inside PROPER. The complete formula is equals PROPER, open parenthesis, TRIM, open parenthesis, A two, then two closing parentheses. PROPER capitalizes the start of each word.
- 4. Copy the formula down
Press Enter. Emma Wilson is cleaned. Copy C two down the remaining rows. Because A two is a relative reference, each copied formula reads the name on its own row.
- Keep the useful original
The original values remain in column A. If a source name changes, the clean version updates. You can review every result before exporting your contact list.
- Two limits worth knowing
TRIM does not remove nonbreaking spaces. PROPER can also change names with unusual capitalization, such as McDonald. Review real names before using this as your final customer list.
- Clean the next import faster
Use TRIM for ordinary spaces, and PROPER for standard word capitalization. What causes more trouble in your imported data: names, dates, or phone numbers? Tell me below.
Preview the input data
| Imported name |
|---|
| emma WILSON |
| NOAH brown |
| mia DAVIS |
| LIAM jones |
| olivia MILLER |
| AVA taylor |
| ethan ANDERSON |
| CHLOE moore |
How to open the CSV
In a blank Google Sheet choose File → Import → Upload and select the downloaded CSV. Use comma as the separator. Start at A1 unless the exercise specifies another location. CSV contains input data; add formulas and controls using the steps above. Files are hosted directly here and require no shared personal Drive document.
Fictional demo data. Formulas use English function names and commas between arguments. Other spreadsheet locales may require semicolons. Check date recognition after importing.