All posts
Lena Okafor 3 min readDataThe blue cell is spilled: it comes from the formula in B2.
Clean messy data with TRIM, PROPER and TEXTSPLIT
Extra spaces, SHOUTY names and “numbers” that won’t add up. Here are the text functions that fix imported data in seconds.
Lena OkaforFrom messy to tidy
Fix spaces and capitals
TRIM removes leading and trailing spaces and squeezes double spaces to one. PROPER capitalises each word. Nest them and you fix both in one go.
TRIM + PROPER
B2fx=PROPER(TRIM(A2))
A
B
1
Imported name
Clean name
2
john SMITH
John Smith
3
MARIA garcia
Maria Garcia
4
li wei
Li Wei
5
aNNa de vries
Anna De Vries
Split one column into many
TEXTSPLIT breaks text at a delimiter and spills the parts into neighbouring cells. TEXTBEFORE and TEXTAFTER grab just one side.
TEXTSPLIT
B2fx=TEXTSPLIT(A2,", ")
A
B
C
1
Full name
Last
First
2
Smith, John
Smith
John
3
Garcia, Maria
Garcia
Maria
4
Okafor, Lena
Okafor
Lena
| Formula | Input | Result |
|---|---|---|
| =TEXTAFTER(A2,"@") | asha@example.com | example.com |
| =TEXTBEFORE(A2,"@") | asha@example.com | asha |
| =LEFT(A2,3) | INV-2026-001 | INV |
| =TEXTAFTER(A2,"-",-1) | INV-2026-001 | 001 |
Numbers stored as text
Left-aligned numbers and a green triangle in the corner mean Excel sees text, so SUM ignores them. Strip the symbols, then convert with VALUE.
Convert text to numbers
B2fx=VALUE(SUBSTITUTE(A2,"$",""))
A
B
1
Amount (text)
Amount
2
$1,200
1,200
3
$850
850
4
$2,045
2,045
5
Total
4,095
Try it in a live spreadsheet
Practise these formulas in interactive lessons with instant grading.
Lena Okafor
Data trainer at FluentExcel
Sara Lind
Marco Ruiz