TRIM and CLEAN functions in Excel

TRIM removes extra spaces from a text, leaving single spaces between words; CLEAN removes characters that cannot be printed.

Data that has been typed by many people, copied from a web page or exported from another program is full of spaces that should not be there. They are invisible, and they break lookups, sorting and duplicate checks, because "Delhi" and "Delhi " are different to Excel. TRIM is the cure.

Syntax

=TRIM(text)

=CLEAN(text)

Argument	What it is
`text`	The text to tidy, or a cell that holds it.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Text as people really type it: stray spaces, mixed capitals, codes with dashes.

	A	B	C	D
1	Full name	Email	Code	Amount as text
2	asha verma	asha.verma@example.com	PB-10-1234	1,250
3	RAVI KUMAR	ravi@example.org	DL-08-0042	300
4	meena gill	meena.gill@example.com	MH-12-9876	45.50
5	Kabir Khan	kabir@example.net	RJ-14-0007	12 kg

=TRIM(A2)
Result: asha verma
" asha verma " loses the spaces at both ends, and the three spaces in the middle become one.
=LEN(A2)-LEN(TRIM(A2))
Result: 5
How many spaces TRIM removed.
=TRIM(A5)
Result: Kabir Khan
"Kabir Khan" has two spaces between the names. TRIM leaves one.
=PROPER(TRIM(A2))
Result: Asha Verma
Tidy the spaces, then fix the capitals.
=TRIM(D4)
Result: 45.50
" 45.50 " with spaces is text. TRIM removes the spaces, but the result is still text.
=VALUE(TRIM(D4))
Result: 45.5
VALUE turns the tidied text into a real number.
=A2=TRIM(A2)
Result: FALSE
A test for stray spaces: FALSE means the cell has spaces that TRIM would remove.

What TRIM does

TRIM makes three changes:

it removes all spaces from the start of the text;
it removes all spaces from the end;
it reduces any run of spaces between words to a single space.

Single spaces between words are kept, so names and sentences stay readable.

How to clean a whole column

TRIM is a formula, so it gives its answer in a different cell. To replace the original data:

In a spare column, write =TRIM(A2) and copy it down.
Select the results and copy them.
Select the original cells and choose Paste Special, then Values.
Delete the helper column.

After that the cleaned text is plain data and no longer depends on a formula.

The space that TRIM cannot remove

Text copied from websites and some programs often contains a non-breaking space. It looks exactly like a space but is a different character, number 160, and TRIM leaves it alone. If TRIM seems not to work, this is nearly always why. Replace it with an ordinary space first:

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

To find out what a mystery character is, =CODE(RIGHT(A2,1)) returns the number of the last character. An ordinary space is 32.

CLEAN

CLEAN removes the non-printing control characters, numbers 0 to 31, that sometimes arrive with imported data. The most familiar is the line break inside a cell. =CLEAN(A2) removes line breaks, and joins the lines without a space. To turn each line break into a space instead, use =SUBSTITUTE(A2, CHAR(10), " ").

A thorough clean-up formula combines all three:

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

Numbers that have spaces

A number with a space beside it is text. TRIM removes the space, but its result is always text too. Follow it with VALUE, as in the sixth example, to get a number that SUM will add.

Inside a lookup

When you cannot clean the source data, trim inside the formula: =VLOOKUP(TRIM(A2), table, 2, FALSE) tidies the value being looked up. If the mess is in the table's first column, the table itself must be cleaned.

Common mistakes

Expecting TRIM to change the original cell. It returns a new value.
Expecting it to remove all spaces. It keeps one between words. To remove every space, use =SUBSTITUTE(A2," ","").
Non-breaking spaces, described above.
Using TRIM on a date or number and getting text back.

In Google Sheets

TRIM and CLEAN work the same way in Google Sheets. Sheets also has a menu command, Data, Data clean-up, Trim whitespace, that trims the selected cells in place.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.