TRIM in Excel: how to use it
TRIM removes spaces at the start and the end, and turns double spaces in the middle into single ones.
Try the first exercises for freeIn the lesson LEN and TRIM
What does TRIM do?
TRIM removes unnecessary spaces from a text. Spaces at the start and the end disappear completely, and where there are two or more spaces between words, only one is left.
You usually can't see extra spaces, but Excel can: to a lookup function, "Leeds " is something different from "Leeds". That's why TRIM is often the first step when you clean up data from another program or from a website.
When do you use TRIM?
- Names from an export, with spaces at the start or the end: =TRIM(A2).
- A VLOOKUP that gives #N/A even though the value seems to be there: look up with =VLOOKUP(TRIM(E2),A2:C50,2,FALSE).
- Double spaces between first name and surname: =TRIM(A2) turns them into one space.
- Cleaning up spaces and capitals in one go: =PROPER(TRIM(A2)).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =TRIM(A1)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
TRIM | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | TRIM |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
text | The cell with the text you want to clean up. | A1 |
TRIM step by step
C1 contains =TRIM(A1). The spaces around it are gone. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C1 and type TRIM with a bracket.
=TRIM( - Click the cell with the text, A1.
=TRIM(A1 - Close the bracket and press Enter: the spaces before and after the word are gone.
=TRIM(A1) - If you want to replace the original column, copy the result and paste it as values over the old column.
More examples
Double spaces in the middle
C1 contains =TRIM(A2). One space is left between the first name and the surname.
Seeing how many spaces are extra
C1 contains =LEN(A2)-LEN(TRIM(A2)). The cell has 12 characters, and only 9 without the extra spaces: there are 3 spaces too many.
Spaces and capitals in one go
C1 contains =PROPER(TRIM(A2)). First the spaces go, then every word gets a capital letter at the start.
The cause of many #N/A errors
To Excel, "Leeds " isn't the same as "Leeds". Compare the length with LEN when a lookup fails for no clear reason.
Where it often goes wrong
- This function doesn't remove a non-breaking space from a web page. For that you need SUBSTITUTE.
Practise this in the lesson LEN and TRIM. The first exercises are free.
Frequently asked questions
How do I overwrite the original column?
Copy the result and paste it as values over the original column.
Read next
LEFT, RIGHT and MIDLENUPPER, LOWER and PROPERSEARCH and FINDJoining text