TRIM vs CLEAN in Excel

TRIM vs CLEAN in Excel comes down to which kind of mess you have. TRIM removes extra spaces: at the start, at the end and the repeats between words. CLEAN removes invisible control characters, such as line breaks and tabs, that sneak in from other systems. Imported data often has both, which is why =TRIM(CLEAN(A2)) is such a common formula. This guide is for anyone whose lookups fail or whose text looks fine but won't match.

TRIM vs CLEAN at a glance#

TRIM deals with the space character; CLEAN deals with characters you can't see at all.

How TRIM works#

TRIM cleans up the normal space character (code 32). It removes every space before the first word and after the last one, and squeezes any run of spaces between words down to a single space:

=TRIM("  Desk   lamp  ")

The result is Desk lamp. TRIM doesn't remove the single space between words, and it doesn't touch any other character. The TRIM function guide has more examples.

How CLEAN works#

CLEAN removes the first 32 characters of the ASCII set, codes 0 to 31. You can't see these characters, but they change how text behaves. The ones you'll meet most often are:

• CHAR(10), a line break. It's what you type with Alt+Enter on Windows (⌃⌥Return on a Mac) to start a new line inside a cell.
• CHAR(13), a carriage return, often paired with a line break in files from other systems.
• CHAR(9), a tab, common in text pasted from other programs.

CLEAN deletes these characters and joins what's left. It does not add a space, which matters when a line break separates two words.

The same messy text, side by side#

Here is one product name exported from a stock system. It has two leading spaces, three spaces in the middle and a line break at the end: " Desk lamp" followed by CHAR(10).

TRIM alone fixed the spaces but left the line break, so the text is still 10 characters and still won't match "Desk lamp" in a lookup. CLEAN alone removed the line break but left the spaces. Together, they produce the clean 9-character name.

When CLEAN glues words together#

Now an address with a line break between two parts: Unit 4 + CHAR(10) + High Street.

=CLEAN(A3)
=TRIM(SUBSTITUTE(A3,CHAR(10)," "))

CLEAN returns Unit 4High Street, because it simply deletes the break. The second formula swaps the line break for a space first, then TRIM tidies up, giving Unit 4 High Street. Use this version when a line break separates words.

How to see what's really in a cell#

Before you choose a fix, find out what the problem is. Three quick checks:

1. Compare lengths. If =LEN(A2) is bigger than =LEN(TRIM(A2)), there are extra spaces. If =LEN(CLEAN(A2)) is smaller than =LEN(A2), there are control characters.
2. Check the last character. =CODE(RIGHT(A2)) shows the code of the last character. 32 is a space, 10 a line break, 9 a tab and 160 a non-breaking space.
3. Test the match directly. =A2="Desk lamp" returns FALSE if anything invisible is in the way.

What neither TRIM nor CLEAN removes#

The non-breaking space, CHAR(160), is the most common character that survives both functions. It's used on web pages to keep words together, so it often arrives when you copy a table from a browser. It looks exactly like a space. Swap it for a normal space, then TRIM:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

For the full clean, combine all three: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). Our comparison of SUBSTITUTE vs REPLACE explains how SUBSTITUTE works.

How to clean a whole column#

TRIM and CLEAN don't change the original cell; they return a cleaned copy. To fix a whole column of imported names in column A:

1. In B2, type =TRIM(CLEAN(A2)) and press Enter.
2. Select B2 down to the last row and press Ctrl+D (⌘D on a Mac) to fill down. Or double-click the fill handle, the small square at the bottom-right of B2.
3. Spot-check a few rows with =LEN(A2) and =LEN(B2) to confirm the extra characters are gone.
4. Copy column B, then paste it over column A as values: Ctrl+Alt+V, then V and Enter on Windows, or ⌃⌘V then Values on a Mac.
5. Delete the helper column.

Step 4 matters: if you delete column A while B still holds formulas, every cleaned value turns into #REF!. Our comparison of Paste vs Paste Values explains why.

When to use TRIM#

Use TRIM whenever spaces are the problem. It is the right choice when:

• Lookups return #N/A for values that look identical, because one has a trailing space. Our comparisons of VLOOKUP vs XLOOKUP and VLOOKUP vs INDEX MATCH both list stray spaces as a top cause of failed matches.
• Names or codes were typed by hand, with double spaces between words.
• COUNTIF or Remove Duplicates treats "Sales" and "Sales " as two different values.

When to use CLEAN#

Use CLEAN when text contains characters you can't see. It is the right choice when:

• Cells show text on two lines that should be on one, or a line-break symbol after export.
• Data came from another system (a CRM, a database export, a PDF) and behaves strangely.
• LEN is larger than the visible text, even after TRIM.

In practice, =TRIM(CLEAN(A2)) is a safe default for any imported column. Build it in a helper column, check the results, then paste them as values over the original. Our guide to cleaning messy data in Excel walks through a full clean-up with PROPER, UPPER and LOWER too.

Common mistakes with TRIM and CLEAN#

These slips cause most cleaning problems:

1. Expecting TRIM to remove line breaks. It only handles spaces. Use CLEAN or SUBSTITUTE.
2. Expecting either to remove CHAR(160). Neither does; use SUBSTITUTE first.
3. Using CLEAN on text where a line break separates words. The words join up. Replace CHAR(10) with a space instead.
4. Cleaning the lookup table but not the lookup value (or the other way round). Both sides must match.
5. Leaving the formulas in place. If you delete the raw column, the cleaned column turns to #REF!. Paste values first.
6. Thinking TRIM changes numbers. TRIM returns text, so a cleaned "250 " becomes the text "250". Use VALUE if you need a number.

Our recommendation#

Use TRIM for spaces, CLEAN for hidden characters, and =TRIM(CLEAN(A2)) as your default for imported data, since it costs nothing to run both. When a line break separates words, swap it for a space with SUBSTITUTE instead of using CLEAN, and add a CHAR(160) substitution for data copied from the web. Both functions work in every version of Excel; if you're comparing versions for other text tools, see which functions Excel 365, 2021 and 2019 include and our comparison of Excel desktop vs Excel for the web.

In short: TRIM for spaces, CLEAN for the invisible, both together for imports.

Practise it#

Try the TRIM(CLEAN()) exercise on this page, then open the cleaning messy text lesson to clean a CRM export in a live sheet. You'll TRIM names, nest TRIM inside PROPER and LOWER, and use SUBSTITUTE to fix phone separators, building clean copies next to the raw data so you can compare.