Clean up messy names in Excel with TRIM and PROPER
Use =PROPER(TRIM(G2)). TRIM removes extra spaces, and PROPER capitalizes the first letter of each word. Messy text like " dana ruiz" becomes "Dana Ruiz".
The contact column in the sample data is a mess on purpose: " dana ruiz", "LUIS ortega ", "priya shah". This is what real exported lists look like.
The functions
| Function | What it does | " priya SHAH" becomes |
|---|---|---|
TRIM | Removes spaces at the ends and turns double spaces into one | priya SHAH |
PROPER | Capital first letter of each word | Priya Shah (after TRIM) |
UPPER | All capitals | PRIYA SHAH |
LOWER | All lowercase | priya shah |
Do it for the whole list
- In an empty column, type
=PROPER(TRIM(G2)). - Double-click the small square at the bottom right of the cell to fill it down.
- To replace the original, copy the new column and use Paste Special, then Values, over the old one.
What these functions miss
- Non-breaking spaces. Text copied from websites often has CHAR(160) spaces that TRIM leaves alone. Use
=TRIM(SUBSTITUTE(G2,CHAR(160)," ")). - Names like McDonald. PROPER gives "Mcdonald". It also capitalizes after apostrophes, so "o'neil" becomes "O'Neil", which is usually right.
- Line breaks. Use CLEAN to remove them.
Why tests include this
Data entry and admin tests often hand you a messy list. Clean text also matters for COUNTIF and VLOOKUP, because "East " with a trailing space doesn't match "East".
Try it: Clean up a name
Column G has contact names typed messily. In C2, show Dana's contact name with extra spaces removed and each word capitalized.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
Does TRIM remove spaces between words?
It keeps single spaces between words and removes any extras, so "priya shah" becomes "priya shah".
Is there a way to do this without formulas?
In Excel, Flash Fill (Ctrl + E) can copy a pattern you type once. Formulas are more reliable for long lists.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How to use COUNTIF in Excel with numbers, text and cell references, plus COUNTIFS for two conditions and a live exercise.
How VLOOKUP works in Excel, why you should always end it with FALSE, how to lock the table with $, its limits, and a live exercise.
A one-page cheat sheet of the Excel formulas that show up on pre-employment tests, with the syntax, a real example and the mistake to avoid for each.