TRIM vs CLEAN
TRIM removes extra spaces; CLEAN removes invisible characters. With imported data they are almost always used together.
| TRIM | CLEAN | |
|---|---|---|
| Leading and trailing spaces | Removes them | No |
| Double spaces inside | Leaves one | No |
| Line breaks and control characters | No | Removes them |
Syntax and example
TRIM
=TRIM(text)
=LEN(TRIM(A2))
Removes leading and trailing spaces and leaves a single space between words.
CLEAN
=CLEAN(text)
=TRIM(CLEAN(A2))
Removes the invisible characters (codes 0 to 31) that come along when copying from other systems.
Which one to use?
With text copied from another system, use both: TRIM(CLEAN(A2)).
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
Cleaning imported data: CLEAN and NUMBERVALUE
Data from another system carries invisible characters that TRIM does not remove; CLEAN deletes them. Amounts with dot thousands and comma decimals ("1.234,50") arrive as text: NUMBERVALUE(text, decimal, group) turns them into numbers whatever the regional settings.
In B2, clean each name: no invisible characters and no extra spaces.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Imported name | Clean name | Amount (text) | Amount | |
| 2 | Ana López | 1.234,50 | |||
| 3 | Luis Pérez | 980,00 | |||
| 4 | Marta Díaz | 12.500,75 | |||
| 5 | Jorge Ruiz | 3.040,10 | |||
| 6 | Sofía Castro | 15,90 | |||
| 7 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What is the difference between TRIM and CLEAN?
- TRIM removes extra spaces; CLEAN removes invisible characters. With imported data they are almost always used together.
- When should I use TRIM or CLEAN?
- With text copied from another system, use both: TRIM(CLEAN(A2)).