Excel practice › Excel functions › TRIM

TRIM in Excel: remove the spaces that break lookups

TRIM removes the spaces at the start and at the end of a text, and cuts any run of spaces between words down to one. =TRIM(A2) turns " Harlow Paper " into "Harlow Paper".

It matters because Excel compares text character by character. A name with one space after it is a different value from the same name without it, so lookups and comparisons fail on cells that look identical.

Skip to the 3 exercises ↓

Syntax

=TRIM(text)

text
The text to clean: a cell, or text in quotes. Single spaces between words are kept. The result is text, also when the cell held a number.
Live exampleA pasted name, cleaned
B2fx
AB
1Vendor (as pasted)Clean
2 Harlow Paper Harlow Paper
3Ridgeline Freight
4 Summit Coffee
5Delta Uniform
6Kestrel Print

Values that look equal and aren't

To Excel, Summit Coffee and Summit Coffee followed by a space are two different texts. A lookup for one doesn't find the other, and nothing on screen shows why: a space at the end of a cell is invisible.

It happens with data that was typed by hand, pasted from an email or exported from another system. In the sheet below, the name in E2 was pasted with a space at the end:

Live example#N/A: a space at the end of the lookup value
F2fx
ABCDEF
1VendorTermsBalanceFind vendorBalance
2Harlow PaperNet 304200Summit Coffee #N/A
3Ridgeline FreightNet 151850
4Summit CoffeeNet 30640
5Delta UniformNet 452975
6Kestrel PrintNet 301120
7Bayside ElectricNet 155310

How to prove the space is there. Count the characters with LEN. If the count is higher than what you can see, something invisible is in the cell:

Live exampleLEN counts what you can't see
F2fx
ABCDEF
1VendorTermsBalanceFind vendorLength
2Harlow PaperNet 304200Summit Coffee 14
3Ridgeline FreightNet 151850
4Summit CoffeeNet 30640
5Delta UniformNet 452975
6Kestrel PrintNet 301120
7Bayside ElectricNet 155310

When the spaces are in the table

Trimming the lookup value doesn't help when the stray spaces are in the column being searched. There are two fixes:

The space TRIM doesn't remove

TRIM removes the ordinary space character. Text copied from web pages often holds a different one, the nonbreaking space (character 160), and Microsoft's page says TRIM by itself does not remove it. If LEN still shows an extra character after TRIM, replace that character with an ordinary space first: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

This case can't be shown live here: the sheets on this page don't run SUBSTITUTE, and their TRIM is less strict than Excel's about that character.

Practice: 3 exercises

Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.

Exercise 1Clean a pasted name

The names in column A were pasted from an email, with stray spaces. In B2, return the name in A2 without them.

B2fx
AB
1Vendor (as pasted)Clean
2 Harlow Paper
3Ridgeline Freight
4 Summit Coffee
5Delta Uniform
6Kestrel Print
Show the answer

=TRIM(A2) gives Harlow Paper.

TRIM drops the spaces at both ends and leaves one between the words: 12 characters instead of 17, and a value that matches the same name anywhere else.

Exercise 2A lookup that survives a stray space

Someone pastes a vendor name in E2, and this time it came with a space in front. In F2, return that vendor's balance. The formula has to work whether E2 has extra spaces or not.

F2fx
ABCDEF
1VendorTermsBalanceFind vendorBalance
2Harlow PaperNet 304200 Delta Uniform
3Ridgeline FreightNet 151850
4Summit CoffeeNet 30640
5Delta UniformNet 452975
6Kestrel PrintNet 301120
7Bayside ElectricNet 155310
Show the answer

=VLOOKUP(TRIM(E2),A2:C7,3,FALSE) gives 2975.

TRIM(E2) hands the lookup the name without the stray space, and that text is in column A. On a clean name TRIM changes nothing, so the formula is safe to use everywhere.

Exercise 3Stray spaces in the table itself

Now the vendor names in column A came out of an export with stray spaces, and the name in E2 is clean. In F2, return the balance for the vendor in E2 without changing column A: trim the names inside the formula.

F2fx
ABCDEF
1VendorTermsBalanceFind vendorBalance
2Harlow Paper Net 304200Ridgeline Freight
3 Ridgeline FreightNet 151850
4Summit CoffeeNet 30640
5Delta Uniform Net 452975
6Kestrel PrintNet 301120
7Bayside Electric Net 155310
Show the answer

=XLOOKUP(E2,TRIM(A2:A7),C2:C7) gives 1850.

TRIM(A2:A7) hands the lookup a cleaned copy of the six names, and XLOOKUP returns the balance from the same position in C2:C7. This needs an Excel version with XLOOKUP and dynamic arrays (Microsoft 365, or Excel 2021 and later). In older versions, clean the column once with a helper column.

Questions people ask

Why is TRIM not removing spaces in Excel?

Most often because the character isn't an ordinary space. Text copied from web pages can hold nonbreaking spaces (character 160), which TRIM leaves alone. Replace them first, then trim: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). The other usual cause: the formula is fine, but the lookup or comparison still points at the original column and not at the trimmed one.

Does TRIM remove the spaces between words?

It keeps one. Spaces at the start and at the end go, and several spaces in a row between words become a single space. Delta Uniform typed with two spaces in the middle comes back with one.

How do I check whether a cell has a trailing space?

Compare its length before and after: =LEN(A2)-LEN(TRIM(A2)) returns the number of extra spaces, and 0 means the cell is clean. =A2=TRIM(A2) answers the same question with TRUE or FALSE: FALSE means there is something to remove.

How do I remove the spaces for good, without keeping the formula?

Put =TRIM(A2) in a helper column and fill it down. Copy the helper column and paste it as values, then delete the original column. The cleaned text is now plain data with no formula behind it.

Keep going

SheetDrill has no lesson on text functions yet. The free skills test is ten questions in a real spreadsheet, from the basics to INDEX/MATCH, and it ends by pointing you to the lesson to start with. No sign-up.

Related

VLOOKUP not working: causes and fixes#N/A error in Excel: meaning and fixesVLOOKUP function, with live examplesXLOOKUP function, with live examplesLEFT, RIGHT and MID functions, with live examplesCONCAT function and the & operator, with live examplesExcel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: TRIM function, How to correct a #N/A error, Top ten ways to clean your data. Last checked November 2026.