| Function category | Text |
| Volatility | Non-volatile |
What does the TRIM function do
The TRIM function allows you to remove extra spaces from text.
It considers spaces at the beginning and end of a cell as extra, as well as double, triple, etc. spaces between words and characters in the middle of a cell. Generally, it refers to any number of spaces more than one between words.
The function often plays an auxiliary role when creating complex compound formulas.
TRIM function syntax
=TRIM(text)| A | B | C | |
|---|---|---|---|
| 1 | Text (· space, ° non-breaking) | TRIM | Length before → after |
| 2 | ··hello···world· | hello·world=TRIM(A2) | 16 → 11 |
| 3 | one··two | one·two=TRIM(A3) | 8 → 7 |
| 4 | text°° | text°°=TRIM(A4) | 6 → 6 |
| 5 | ·· | =TRIM(A5) | 2 → 0 |
TRIM removes spaces at the start and end and leaves one space between words. The non-breaking space (code 160) from web pages isn’t touched — use =TRIM(SUBSTITUTE(A4,CHAR(160)," ")).
TRIM formula examples
See examples:
REPT function in Excel
Add symbols before/after each word
Remove last word in Excel
Compress spaces without the TRIM function
Sometimes you want to remove spaces in selected cells without formulas and columns. Unfortunately, Excel's standard set of procedures doesn't have this capability.
But if your Excel has the !SEMTools add-in connected - it does! Read more in the article:
Like the article? Help its author! Buy !SEMTools, it has lots of useful instruments to process text data.