CLEAN vs TRIM in Excel: What’s the Difference?
Text copied or imported into Excel can contain more than the words you see. Extra spaces, tabs and line breaks may make your data inconsistent and can interfere with matching, filtering or analysis.
Excel provides two useful functions for cleaning this type of text: TRIM and CLEAN. Although they are often used together, they solve different problems.
In this tutorial, you’ll learn what each function removes, when to use it and how to combine both functions in the correct order. You can also download the free practice workbook and try the examples yourself.
CLEAN vs TRIM: a quick comparison
Function | What it removes | What it does not remove |
TRIM | Leading and trailing regular spaces, plus repeated regular spaces between words | Tabs, line feeds and other non-printing control characters |
CLEAN | The first 32 non-printing ASCII control characters—codes 0 through 31 | Extra regular spaces and characters outside that range, such as CHAR(160) |
In simple terms:
TRIM removes extra regular spaces, while CLEAN removes the first 32 non-printing ASCII control characters. The ones you are most likely to encounter in Excel are tabs and line feeds.
What does the TRIM function do?
The TRIM function removes unnecessary regular spaces from text. It removes spaces from the beginning and end of the text and reduces multiple spaces between words to a single space.
Syntax
=TRIM(text)
For example, if cell B5 contains extra spaces before, after and between the words, you can use:
=TRIM(B5)The result will contain one regular space between each word, with no leading or trailing regular spaces.
However, TRIM only works with the regular ASCII space character, code 32. It does not remove tabs, line feeds or other non-printing characters.
What does the CLEAN function do?
The CLEAN function removes the first 32 non-printing characters in the ASCII character set—codes 0 through 31.
Syntax
=CLEAN(text)Some control characters you may encounter in imported data include:
Character | ASCII code | Purpose |
Tab | 9 | Moves text to the next tab position |
Line feed | 10 | Moves text down to the next line |
Carriage return | 13 | Moves the cursor back to the beginning of the current line |
For example:
=CLEAN(B5)This removes these control characters, but it does not remove extra regular spaces.
Which ASCII control characters does CLEAN remove?
CLEAN was designed to remove the first 32 non-printing characters in the 7-bit ASCII character set—codes 0 through 31.
Most of these control characters originated from early computer and data-communication systems and are rarely encountered in ordinary Excel work. The ones Excel users are most likely to see are horizontal tabs, line feeds and carriage returns.
Because these are non-printing control characters, they usually do not have a visible appearance. Instead, they affect how text or data is interpreted.
ASCII code | Abbreviation | Control character |
0 | NUL | Null |
1 | SOH | Start of Heading |
2 | STX | Start of Text |
3 | ETX | End of Text |
4 | EOT | End of Transmission |
5 | ENQ | Enquiry |
6 | ACK | Acknowledge |
7 | BEL | Bell |
8 | BS | Backspace |
9 | HT | Horizontal Tab |
10 | LF | Line Feed |
11 | VT | Vertical Tab |
12 | FF | Form Feed |
13 | CR | Carriage Return |
14 | SO | Shift Out |
15 | SI | Shift In |
16 | DLE | Data Link Escape |
17 | DC1 | Device Control 1 |
18 | DC2 | Device Control 2 |
19 | DC3 | Device Control 3 |
20 | DC4 | Device Control 4 |
21 | NAK | Negative Acknowledge |
22 | SYN | Synchronous Idle |
23 | ETB | End of Transmission Block |
24 | CAN | Cancel |
25 | EM | End of Medium |
26 | SUB | Substitute |
27 | ESC | Escape |
28 | FS | File Separator |
29 | GS | Group Separator |
30 | RS | Record Separator |
31 | US | Unit Separator |
A note about CHAR(0)
ASCII code 0 is called NUL or Null, but it should not be written as CHAR(0) in an Excel example. Excel’s CHAR function only accepts numbers from 1 through 255, so CHAR(0) returns a #VALUE! error.
For practical Excel work, the most useful examples are:
CHAR(9) — horizontal tab
CHAR(10) — line feed
CHAR(13) — carriage return
CLEAN removes these characters when they are present in the text.
Why should CLEAN come before TRIM?
When a cell contains both control characters and extra spaces, use:
=TRIM(CLEAN(B5))
Excel evaluates the inner function first:
CLEAN(B5)removes tabs, line feeds and other control characters covered by CLEAN.
TRIM(...)removes the extra regular spaces that remain.
For example, imagine that B5 contains spaces, followed by a tab, the words Customer Support, a line feed and more spaces. CLEAN first removes the tab and line feed. TRIM then removes the unnecessary spaces and returns:
Customer Support
You may also see this formula written in the opposite order:
=CLEAN(TRIM(B5))It may work for some text, but it is less reliable. Removing the control characters after TRIM can bring previously separated spaces together. Using CLEAN first allows TRIM to tidy the final result.
An important limitation
CLEAN does not remove every invisible or non-printing character. It targets codes 0 through 31. For example, the non-breaking space represented by
CHAR(160)
is outside that range and is not removed by either CLEAN or the standard TRIM function.
If your text contains non-breaking spaces, you can replace them with regular spaces before trimming:
=SUBSTITUTE(B5,CHAR(160)," ")OR
=SUBSTITUTE(B5," "," ")For text that may contain control characters as well, you can combine the functions:
=TRIM(CLEAN(SUBSTITUTE(B5,CHAR(160)," ")))OR
=TRIM(CLEAN(SUBSTITUTE(B5," "," ")))Download the free CLEAN vs TRIM workbook
The free workbook includes:
The examples demonstrated in the video
A simple CLEAN vs TRIM comparison
A Practice worksheet for testing both functions
A Quick Reference worksheet for review
Use the Practice worksheet to try the formulas yourself and decide whether each example requires CLEAN, TRIM or both functions.
Want more hands-on Excel practice?
If you already understand the basics of PivotTables and want more structured practice, explore the Excel PivotTables Practice Program.
The program includes:
10 focused mini practices
Five realistic business scenarios
Downloadable Excel files
Step-by-step video walkthroughs
It is designed to help you build confidence using PivotTables to solve practical workplace problems—not just follow demonstrations.
Final takeaway
TRIM and CLEAN are not interchangeable:
Use TRIM to remove extra regular spaces.
Use CLEAN to remove ASCII control characters with codes 0 through 31.
Use
=TRIM(CLEAN(B5))when your text contains both.
Download the free workbook, open the Practice worksheet and test the functions with the included examples.
Comments