Excel + Google Sheets · TRIM + PROPER
Clean messy names in one go
Fixes capitals and removes extra spaces from a whole column at once.
The formula
=TRIM(PROPER(A2))What each part means
PROPER(A2)Capitalizes each wordTRIM(...)Removes extra spaces
Example
| A | B | |
|---|---|---|
| 1 | Imported names | Clean |
| 2 | jOHN smith | |
| 3 | MARIA lopez | |
| 4 | ana GARCÍA | |
| 5 | pedro MARTIN | |
| 6 | lucía fernández |
| A | B | |
|---|---|---|
| 1 | Imported names | Clean |
| 2 | jOHN smith | John Smith |
| 3 | MARIA lopez | Maria Lopez |
| 4 | ana GARCÍA | Ana García |
| 5 | pedro MARTIN | Pedro Martin |
| 6 | lucía fernández | Lucía Fernández |
Step by step
- Click cell B2.
- Type the formula:
=TRIM(PROPER(A2)) - Press Enter.
- Drag the small square in the bottom-right corner of the cell down to copy it to the other rows.
Where it works
- Every version of Excel
- Google Sheets
- Excel for the web (free)
The same formula in other languages
- English TRIM + PROPER
- Español ESPACIOS + NOMPROPIO
- Português ARRUMAR + PRI.MAIÚSCULA