Excel function guide · Text
TRIM function in Excel
TRIM removes spaces from the start and end of text and reduces any run of spaces between words to a single space. It's the first fix when a lookup returns #N/A or a count looks too low on imported data.
Syntax
=TRIM(text)
| Argument | Required? | What it means |
|---|---|---|
| text | Required | The text or cell to clean. |
TRIM examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | |
|---|---|---|
| 1 | Raw name | Team |
| 2 | ··Sara·Ahmed | Sales |
| 3 | Tom···Baker· | Finance |
| 4 | lina·COSTA | HR |
| 5 | Yusuf·Demir·· | Sales |
· marks a space, so you can see the extra ones.
1. Leading spaces
=TRIM(A2)
Result: Sara Ahmed
The two spaces before Sara are removed.
2. Spaces in the middle and at the end
=TRIM(A3)
Result: Tom Baker
Three spaces between the names become one, and the trailing space goes.
3. Clean and capitalise
=PROPER(TRIM(A4))
Result: Lina Costa
TRIM runs first, then PROPER capitalises the first letter of each word.
Try it now
Practise TRIM in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In C2, remove the extra spaces from the name in A2 with TRIM.
Common TRIM mistakes
Non-breaking spaces
Text copied from web pages often contains non-breaking spaces (character 160), which TRIM leaves alone. Replace them first: TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
Expecting the original to change
TRIM returns a cleaned copy in another cell. To replace the original column, copy the results and paste them as values.
Line breaks and odd characters
TRIM removes spaces only. Use CLEAN for line breaks and other non-printing characters, e.g. TRIM(CLEAN(A2)).
Checking by eye
Extra spaces are invisible. Compare LEN(A2) with LEN(TRIM(A2)): if they differ, the cell has extra spaces.
Practise this in Excel Quest
Clean a customer list Members
Course 7 · Text & Data Cleaning · 12 min
Standardise names, emails, postcodes and notes, then spot duplicates that only differed by case and spacing.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.