How to Use LEN, TRIM, and CLEAN in Excel

Introduction

You paste customer names from a CRM, and half of them won't match in VLOOKUP. The names look right, but some have extra spaces or non-printable characters that break the match. LEN finds them, TRIM removes extra spaces, and CLEAN strips non-printable characters.

The Dataset

Here's a customer list pasted from an export. The names look fine:

	A
3	Customer
4	Sarah Jones
5	James Wilson
6	Maria Lopez
7	Li Chen
8	Alex Kim

But some of these cells have hidden spaces that aren't visible in the spreadsheet.

LEN

LEN returns the number of characters in a cell, including spaces you can't see. The syntax is:

=LEN(text)

• text: The cell or string to measure.

Running LEN on each name tells a different story than the data suggests:

Customer	Expected Length	LEN Result
Sarah Jones	11	14
James Wilson	12	14
Maria Lopez	11	14
Li Chen	7	7
Alex Kim	8	10

"Sarah Jones" is 11 characters, but LEN returns 14. Three extra characters are hiding in the cell. "Li Chen" returns 7, which matches. The rest are all off.

Here's what's actually in each cell:

Customer	LEN	Issue
Sarah Jones	14	2 leading spaces, 1 trailing space
James Wilson	14	2 trailing spaces
Maria Lopez	14	1 leading space, double space between words, 1 trailing space
Li Chen	7	Clean
Alex Kim	10	2 leading spaces

LEN doesn't fix anything on its own, but it's the fastest way to confirm whether a cell is clean. If the number is higher than expected, something is hiding in there.

TRIM

TRIM removes leading spaces, trailing spaces, and reduces any runs of multiple spaces between words down to a single space. The syntax is:

=TRIM(text)

• text: The cell or string to clean up.

To clean up the first name:

=TRIM(A4)

Here's every name before and after TRIM:

Customer	LEN Before	After TRIM	LEN After
Sarah Jones	14	Sarah Jones	11
James Wilson	14	James Wilson	12
Maria Lopez	14	Maria Lopez	11
Li Chen	7	Li Chen	7
Alex Kim	10	Alex Kim	8

All five names now have the correct character count. The double space between "Maria" and "Lopez" is reduced to one. Cells that were already clean (Li Chen) pass through unchanged.

CLEAN

CLEAN removes non-printable characters, which are characters with ASCII codes 0 through 31. These include line breaks (code 10), carriage returns (code 13), and tabs (code 9). They show up when you paste data from websites, CSV files, or other systems. The syntax is:

=CLEAN(text)

• text: The cell or string to clean.

If a cell contains "Sarah Jones" followed by a hidden line break, it might display correctly but fail lookups. CLEAN strips the line break:

=CLEAN(A4)

Unlike TRIM, CLEAN doesn't touch spaces. A cell with leading spaces and a line break needs both:

=TRIM(CLEAN(A4))

CLEAN targets character codes 0 through 31. Non-breaking spaces from web data (code 160) need a different fix: SUBSTITUTE(A4, CHAR(160), " ").

Combining TRIM and CLEAN

For data from external sources, =TRIM(CLEAN(A4)) is the standard cleanup formula. CLEAN strips non-printable characters first, then TRIM handles the spaces.

Function	What It Removes
TRIM	Leading spaces, trailing spaces, extra spaces between words
CLEAN	Non-printable characters (codes 0-31)
TRIM(CLEAN())	Both

You can wrap this combination around lookup values to prevent #N/A errors from dirty data. Instead of:

=VLOOKUP(A4, D4:E8, 2, FALSE)

Use:

=VLOOKUP(TRIM(CLEAN(A4)), D4:E8, 2, FALSE)

If the lookup value or the table has hidden characters, this handles them before the match runs.

Key takeaway: When a VLOOKUP or COUNTIF returns unexpected results, check LEN first. If the character count is off, wrap the value in TRIM, CLEAN, or both.

Conclusion

LEN to diagnose, TRIM for spaces, CLEAN for non-printable characters. Use TRIM(CLEAN()) as your default cleanup formula when working with imported data.

For more text functions, check out our guide on LEFT, RIGHT, and MID for extracting text. For replacing specific characters that CLEAN doesn't cover, see our guide on TRIM, CLEAN, and SUBSTITUTE.