Clean a CRM name export (TRIM + PROPER)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
The CRM export in A2:A4 brings padding and random capitalisation. Put a clean version of the name in A2 into the Clean name cell B2: no leading or trailing spaces, and each word capitalised.
Do not edit column A — the next export would just bring the mess back.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | |
|---|---|---|
| 1 | Name (as exported) | Clean name |
| 2 | ana LEE | |
| 3 | CLEO wong | |
| 4 | bob |
Notes
TRIM removes leading and trailing spaces (and collapses runs of spaces between words), while PROPER capitalises the first letter of each word and lowercases the rest. Together they turn " ana LEE " into "Ana Lee".
How to approach
Your answer goes in B2.
- Target cell is B2
- Two functions: one for spaces, one for capitalisation
- Order does not matter here, but the nested form is easier to read
- Shape: =PROPER(TRIM(?))
FAQ
How do I fix names exported with random capitals?
=PROPER(TRIM(cell)). PROPER capitalises each word; TRIM removes the padding the export added.
Does PROPER break names like McDonald or van der Berg?
Yes — it capitalises every word. For those, clean with TRIM and fix the exceptions by hand; no formula knows which capitals are intentional.