AaText16 min read ·

How to Use TRIM in Excel

Learn the Excel TRIM function with real examples. Remove leading, trailing and extra spaces, clean data for lookups, and fix TRIM not working.

Quick answer

=TRIM(text)

TRIM removes the spaces at the start and end of a text and turns every run of spaces between words into one, like =TRIM(A2). If spaces are left over, they're usually non-breaking spaces from a web page, which =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) removes.

On this page ⌄
  1. The TRIM syntax
  2. A basic TRIM example
  3. Check for extra spaces with LEN
  4. Fix spacing and capitals: TRIM with PROPER, UPPER and LOWER
  5. TRIM a whole column at once
  6. Replace the original data with the trimmed text
  7. Use TRIM in XLOOKUP, INDEX MATCH and VLOOKUP
  8. Use TRIM with COUNTIF and UNIQUE
  9. TRIM not working? Remove non-breaking spaces
  10. Remove line breaks and hidden characters with CLEAN
  11. Remove all spaces with SUBSTITUTE
  12. Remove extra spaces without a formula
  13. TRIM returns text, not numbers
  14. Common TRIM mistakes
  15. Frequently asked questions about TRIM
  16. Which versions of Excel have TRIM?
  17. Key takeaways

TRIM removes extra spaces from text. It deletes the spaces at the start and end of a cell and turns every run of spaces between words into a single space. It's the first fix for names and codes pasted from another system, where spaces you can't see stop lookups from matching and make the same value look like two different ones:

=TRIM(text)

TRIM has one argument and works in every version of Excel. It only removes the regular space character, so when it seems not to work, the cause is usually a non-breaking space from a web page or a line break. Both are covered below, along with CLEAN and SUBSTITUTE, which deal with them.

The examples use this customer export in A1:D6:

A: Full nameB: EmailC: PhoneD: Order code
2emma JOHNSON[email protected](555) 201-4433NW-2026-0187
3liam o'brien[email protected]555.388.1902CT-2025-0942
4OLIVIA chen[email protected]555-647-2210FB-2026-0033
5noah patel[email protected](555) 903 7751NW-2026-0451
6Sofia Martinez[email protected]555 412 0098AW-2024-1120

The names are messy on purpose. A2 has two spaces before emma, three between the words and one after JOHNSON. A4 ends with two spaces, and A5 has three spaces between noah and patel. A3 and A6 are already tidy. You can't see the spaces in the table, but Excel counts every one.

The TRIM syntax

ArgumentWhat it meansExample
textThe text to remove spaces from: a cell, text in quotes, another formula, or a whole range in Microsoft 365 and Excel 2021 or laterA2

Here's what TRIM does with each kind of space:

Where the spaces areWhat TRIM does
Before the first wordRemoves all of them
After the last wordRemoves all of them
Two or more between wordsKeeps one
One between wordsKeeps it
Nothing but spaces in the cellReturns empty text

So TRIM tidies text you type in quotes too:

=TRIM(" monthly sales report ")→ monthly sales report

TRIM only removes the space you type with the space bar, character 32. Characters that look like spaces, such as non-breaking spaces, tabs and line breaks, stay where they are. TRIM also never changes letters, so the capitals come out exactly as they went in.

A basic TRIM example

What's the name in A5 without the extra spaces?

=TRIM(A5)→ noah patel

The three spaces between the words become one. TRIM removes the two spaces at the end of A4 the same way:

=TRIM(A4)→ OLIVIA chen

That result looks just like the original, because a space at the end of a cell is invisible. TRIM has still changed it, and the change matters: to Excel, A4 with its two spaces isn't equal to OLIVIA chen without them, so a lookup or a count for one won't find the other. LEN shows the difference.

Check for extra spaces with LEN

LEN counts the characters in a cell, spaces included. Compare the length before and after TRIM to see what it removed:

=LEN(A4)→ 13
=LEN(TRIM(A4))→ 11
=LEN(A2)-LEN(TRIM(A2))→ 5

Here are all five names, with each space shown as a dot:

A: Full nameLENAfter TRIMLEN after TRIM
2··emma···JOHNSON·17emma·JOHNSON12
3liam·o'brien12liam·o'brien12
4OLIVIA·chen··13OLIVIA·chen11
5noah···patel12noah·patel10
6Sofia·Martinez14Sofia·Martinez14

A2 has six spaces. TRIM keeps one, between emma and JOHNSON, and removes the other five. A3 and A6 come back unchanged.

To test whether a cell needs cleaning, compare it with its trimmed version. TRUE means there's nothing to trim:

=A4=TRIM(A4)→ FALSE
=A6=TRIM(A6)→ TRUE

How many names in the column need cleaning?

=SUMPRODUCT(--(A2:A6<>TRIM(A2:A6)))→ 3

The comparison gives TRUE for A2, A4 and A5. -- turns the TRUEs and FALSEs into 1s and 0s, and SUMPRODUCT adds them up. It works in every version of Excel. To count the extra spaces in the whole column instead, =SUMPRODUCT(LEN(A2:A6)-LEN(TRIM(A2:A6))) returns 9: five from A2 and two each from A4 and A5.

Fix spacing and capitals: TRIM with PROPER, UPPER and LOWER

TRIM only fixes spaces. The names also have messy capitals, so wrap TRIM in PROPER, which capitalizes the first letter of each word and lowercases the rest:

=PROPER(TRIM(A5))→ Noah Patel
=PROPER(TRIM(A4))→ Olivia Chen

UPPER and LOWER work the same way, for all capitals or none:

=UPPER(TRIM(A5))→ NOAH PATEL
=LOWER(TRIM(A4))→ olivia chen

The order doesn't matter here. =TRIM(PROPER(A4)) returns the same Olivia Chen, because one function changes only the spaces and the other only the letters. TRIM on the inside reads naturally, though: clean the spaces, then fix the capitals.

PROPER capitalizes any letter that follows something other than a letter. That's why it gets the apostrophe in A3 right:

=PROPER(A3)→ Liam O'Brien

The same rule turns it's into It'S and 2nd into 2Nd, and PROPER can't know that mcdonald should be McDonald. Check names like these by eye.

Your turn. The sheet below is live and uses the same customer export. The exercise asks you to clean the messiest name, A2, which has extra spaces at the start, in the middle and at the end. Fix the spacing and the capitals in one formula, then press Enter.

Try it yourself

A live spreadsheet. Type a formula and press Enter to check it.

Exercise 1 of 11TRIMPROPER

Clean the name in A2: remove the extra spaces and capitalize it properly.

Write your formula in the green cell, G2.

customer-export.xlsx
fx
1Full nameEmailPhoneOrder code
2 emma JOHNSON [email protected](555) 201-4433NW-2026-0187Clean name
3liam o'brien[email protected]555.388.1902CT-2025-0942
4OLIVIA chen [email protected]555-647-2210FB-2026-0033
5noah patel[email protected](555) 903 7751NW-2026-0451
6Sofia Martinez[email protected]555 412 0098AW-2024-1120
7
8
9
10

Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.

TRIM a whole column at once

In Microsoft 365 and Excel 2021 or later, give TRIM the whole column and it returns one result per row. Type this in F2:

=TRIM(A2:A6)→ emma JOHNSON, liam o'brien, OLIVIA chen, noah patel, Sofia Martinez

The five trimmed names spill into F2:F6. Wrap the formula in PROPER, UPPER or LOWER in the same way, or in LEN to check the new lengths: =LEN(TRIM(A2:A6)) spills 12, 12, 11, 10 and 14.

If any cell in F3:F6 already holds something, you get #SPILL! instead. Clear those cells and the list appears. In Excel 2019 and older, type =TRIM(A2) in F2 and copy it down to F6 instead. Excel changes A2 to A3, A4 and so on as it goes.

Replace the original data with the trimmed text

A TRIM formula can't clean the cell it's in: typing =TRIM(A2) into A2 itself is a circular reference. So TRIM always puts the clean text in another column, and to replace the original, you paste the results back as values:

  1. Put the formula in an empty column, such as =TRIM(A2:A6) in F2, or =TRIM(A2) copied down to F6.
  2. Select F2:F6 and copy it with Ctrl+C.
  3. Right-click A2 and choose Paste Special > Values, or press Ctrl+Alt+V, then V, then Enter.
  4. Delete the formulas in column F.

Pasting values replaces each formula with its result, so A2:A6 now hold the trimmed text and no longer depend on column F. Don't use a plain paste here. It copies the formulas instead, and since they'd now point five columns to the left of column A, you get #REF!.

Use TRIM in XLOOKUP, INDEX MATCH and VLOOKUP

Extra spaces are one of the most common causes of #N/A. A lookup for olivia chen doesn't find A4, because A4 ends with two spaces. XLOOKUP ignores upper and lower case, but not spaces. With olivia chen typed in G1:

=XLOOKUP(G1, A2:A6, B2:B6)→ #N/A
=XLOOKUP(G1, TRIM(A2:A6), B2:B6)→ [email protected]

TRIM(A2:A6) gives XLOOKUP a trimmed copy of the names to search, and the data on the sheet stays as it is. The spaces can be on the other side too. Say G1 holds liam o'brien with a space typed at the end. Now the name in the table is clean and the lookup value isn't, so trim the lookup value instead:

=XLOOKUP(G1, A2:A6, B2:B6)→ #N/A
=XLOOKUP(TRIM(G1), A2:A6, B2:B6)→ [email protected]

When either side could have stray spaces, trim both: =XLOOKUP(TRIM(G1), TRIM(A2:A6), B2:B6). The XLOOKUP guide covers the rest of the function.

MATCH takes a trimmed range the same way:

=MATCH("noah patel", A2:A6, 0)→ #N/A
=MATCH("noah patel", TRIM(A2:A6), 0)→ 4
=INDEX(D2:D6, MATCH("noah patel", TRIM(A2:A6), 0))→ NW-2026-0451

noah patel is the 4th name in the list, so INDEX returns the 4th order code. In Excel 2019 and older, press Ctrl+Shift+Enter instead of Enter for formulas that trim a range like this. The INDEX and MATCH guide explains how the two work together.

For VLOOKUP, trim the lookup value. With liam o'brien and a trailing space in G1 again:

=VLOOKUP(TRIM(G1), A2:D6, 4, FALSE)→ CT-2025-0942

You can trim the table inside VLOOKUP too, but TRIM turns every column it touches into text, numbers included. To trim the data side, XLOOKUP or INDEX and MATCH are better, because they only trim the column you search. On large sheets, it's faster still to clean the data once, as shown above, than to trim the whole range every time the formula recalculates. The VLOOKUP guide has a checklist for other causes of #N/A.

Use TRIM with COUNTIF and UNIQUE

Counts and unique lists treat a name with extra spaces as a different name. Say a second order from Noah lands in row 7, typed noah patel with a single space. To Excel, A5 and A7 are two different names:

=COUNTIF(A2:A7, "noah patel")→ 1
=COUNTA(UNIQUE(A2:A7))→ 6

COUNTIF finds only row 7, and UNIQUE lists both versions of Noah. COUNTIF can't take TRIM around its range: Excel won't accept =COUNTIF(TRIM(A2:A7), "noah patel"), because COUNTIF needs cells on the sheet, not a calculated list. Compare the trimmed names directly instead, and add up the matches with SUMPRODUCT:

=SUMPRODUCT(--(TRIM(A2:A7)="noah patel"))→ 2

A wildcard works too. * stands for any characters, spaces included, so this matches both rows:

=COUNTIF(A2:A7, "noah*patel")→ 2

It would also count noah j. patel, though, so the SUMPRODUCT version is stricter. The COUNTIF guide covers wildcards in detail.

UNIQUE does accept TRIM, so the duplicate goes away:

=UNIQUE(TRIM(A2:A7))→ emma JOHNSON, liam o'brien, OLIVIA chen, noah patel, Sofia Martinez
=COUNTA(UNIQUE(TRIM(A2:A7)))→ 5

UNIQUE already ignores upper and lower case, so TRIM is the only fix it needs here. The UNIQUE guide has more on listing and counting unique values.

TRIM not working? Remove non-breaking spaces

Text copied from a web page often contains non-breaking spaces. They look exactly like spaces, but they're a different character, number 160, and TRIM leaves them alone. Say H2 holds Sofia Martinez copied from a website, with a non-breaking space at the end. It looks the same as A6, but:

=H2=A6→ FALSE
=LEN(H2)→ 15
=LEN(TRIM(H2))→ 15

TRIM removed nothing. To see which character is there, check its code. CODE returns the number of the first character, so give it the last one with RIGHT:

=CODE(RIGHT(H2))→ 160
=CODE(RIGHT(A4))→ 32

160 is a non-breaking space, and 32, from the end of A4, is a regular one. The fix is to swap the non-breaking spaces for regular ones with SUBSTITUTE, then trim:

=TRIM(SUBSTITUTE(H2, CHAR(160), " "))→ Sofia Martinez
=TRIM(SUBSTITUTE(H2, CHAR(160), " "))=A6→ TRUE

CHAR(160) is the non-breaking space, and " " is a regular one. Replace them with a space rather than with nothing, because web pages also put them between words, and removing those would join the words together. The same formula fixes lookups on pasted data: =XLOOKUP(TRIM(SUBSTITUTE(H2, CHAR(160), " ")), A2:A6, B2:B6) returns [email protected].

Remove line breaks and hidden characters with CLEAN

Line breaks are another character TRIM doesn't touch. Data from forms and other systems often has them, and so does any cell where someone pressed Alt+Enter. CLEAN removes them, along with the other nonprinting characters, codes 0 to 31. Say H3 holds Sofia Martinez with a line break at the end:

=LEN(H3)→ 15
=LEN(TRIM(H3))→ 15
=LEN(TRIM(CLEAN(H3)))→ 14

Put CLEAN inside and TRIM outside, so TRIM tidies any spaces that were sitting next to the removed characters. For text that might have all three problems, combine everything:

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

CLEAN doesn't remove non-breaking spaces either, which is why SUBSTITUTE is still in there.

Remove all spaces with SUBSTITUTE

TRIM always keeps one space between words. To remove every space, replace the space with nothing:

=SUBSTITUTE(C6, " ", "")→ 5554120098
=SUBSTITUTE(C5, " ", "")→ (555)9037751

That's useful for phone numbers, codes and IDs, where no space belongs. Don't use it on names, because it joins the words: =SUBSTITUTE(A5, " ", "") returns noahpatel.

SUBSTITUTE and LEN together also count every space in a cell, the single ones included:

=LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))→ 6

Like TRIM, SUBSTITUTE returns text, even when only digits are left. That's right for phone numbers, which you never do math with. For numbers you do need, see TRIM returns text, not numbers below.

Remove extra spaces without a formula

Find & Replace, Ctrl+H, cleans the cells where they are, with no helper column. It has no setting for "extra" spaces, though, so it does one job at a time:

  • Remove every space: select the cells, type a space in Find what, leave Replace with empty, and click Replace All. Like SUBSTITUTE, this joins words together.
  • Shrink double spaces: type two spaces in Find what and one in Replace with, then click Replace All until Excel finds nothing to replace. A single space at the start or end stays, so A4 would still end with one space.
  • Non-breaking spaces: click in Find what, hold Alt and type 0160 on the numeric keypad, then type a regular space in Replace with.
  • Line breaks: click in Find what and press Ctrl+J, then type a space in Replace with.

For names, a TRIM column and Paste Special > Values is more reliable, because TRIM handles the spaces at the start, in the middle and at the end in one go.

TRIM returns text, not numbers

TRIM is a text function, so it always returns text, even when every character is a digit. Say H5 holds a quantity pasted from a report as text, with a space on each side of 250. TRIM removes the spaces, but the result is the text 250, not the number:

=TRIM(H5)→ 250
=TRIM(H5)=250→ FALSE
=VALUE(TRIM(H5))→ 250
=--TRIM(H5)→ 250

The text "250" doesn't equal the number 250, SUM skips it, and a lookup for the number 250 won't find it. Wrap TRIM in VALUE, or put two minus signs in front, to turn the result into a number. For numbers copied from a web page, swap the non-breaking spaces first: =VALUE(TRIM(SUBSTITUTE(H5, CHAR(160), " "))).

It works the other way too. TRIM on a cell that already holds a number returns that number as text, and on a date it returns the date's serial number as text, such as 46343 for November 17, 2026. Use TRIM on text columns only. The LEFT, RIGHT and MID guide has more on text that looks like a number.

Common TRIM mistakes

ProblemWhat's going onFix
TRIM leaves a spaceIt's a non-breaking space, character 160, usually from a web page=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
Text still breaks onto two linesA line break isn't a spaceCLEAN, or SUBSTITUTE CHAR(10) with a space
Words run together, as in noahpatelSUBSTITUTE, Find & Replace or CLEAN removed the space between wordsUse TRIM for extra spaces, and replace line breaks with a space
Circular reference warningTRIM points at its own cell, as in =TRIM(A2) typed in A2Put the formula in another column
#REF! after cleaning the dataThe formulas were pasted instead of their values, or the column they point at was deletedUse Paste Special > Values before deleting anything
A lookup still returns #N/AOnly one side is trimmed, or the spaces are non-breakingTRIM the lookup value and the range, and check for CHAR(160)
Excel won't accept COUNTIF with TRIMCOUNTIF's range must be cells, not a calculated listUse SUMPRODUCT with TRIM instead
A number won't add up or matchTRIM returns textWrap it in VALUE, or put -- in front
A date turns into a 5-digit numberTRIM returns the date's serial number as textDon't TRIM dates or numbers
#SPILL!=TRIM(A2:A6) needs empty cells below itClear the cells, or move the formula

Frequently asked questions about TRIM

What's the difference between TRIM and CLEAN?

TRIM removes extra spaces. CLEAN removes nonprinting characters, codes 0 to 31, such as line breaks and tabs. Neither removes the other's characters, and neither removes non-breaking spaces. For text from another system, use them together, as in =TRIM(CLEAN(A2)).

How do I find cells that look empty but contain spaces?

A cell with nothing but spaces looks blank, but it isn't. ISBLANK returns FALSE for it, and COUNTA counts it. Trim it and compare the result with empty text. With three spaces in H6:

=ISBLANK(H6)→ FALSE
=LEN(H6)→ 3
=TRIM(H6)=""→ TRUE

To count the cells in a range that look blank, truly empty ones included, use =SUMPRODUCT(--(TRIM(range)="")).

How do I highlight cells that have extra spaces?

Use conditional formatting with the same test as above. Select A2:A6, choose Home > Conditional Formatting > New Rule, then Use a formula to determine which cells to format, and enter:

=A2<>TRIM(A2)

Pick a fill color and click OK. A2, A4 and A5 are highlighted. Leave out the dollar signs, so the rule checks each row against its own cell.

Does Power Query's Trim do the same thing?

No. In Power Query, Transform > Format > Trim only removes the spaces at the start and end. Repeated spaces between words, like the three in A5, stay. If those matter, load the data into Excel and use the TRIM function on it.

Which versions of Excel have TRIM?

TRIM, CLEAN, SUBSTITUTE and PROPER work in every version of Excel, on Windows and Mac, and in Excel for the web. Google Sheets has all four too, with the same arguments. Giving TRIM a whole range, as in =TRIM(A2:A6), needs Microsoft 365 or Excel 2021 or later, and so do XLOOKUP and UNIQUE. In Excel 2019 and older, copy =TRIM(A2) down instead, and confirm formulas like =MATCH("noah patel", TRIM(A2:A6), 0) with Ctrl+Shift+Enter. The SUMPRODUCT formulas work in every version as they are.

Don't confuse TRIM with TRIMRANGE. Despite the name, TRIMRANGE has nothing to do with spaces. It drops the empty rows and columns from the edges of a range, so a formula that points at a whole column only uses the part with data. It's only available in Microsoft 365.

For Microsoft's own examples, see the TRIM function, CLEAN function, SUBSTITUTE function and PROPER function pages.

For more practice, the text topic has the free clean-name exercise on the same customer export, plus Premium exercises on a first name from a messy cell, a label built from a code and a clean name, replacing characters with SUBSTITUTE and counting characters with LEN.

Key takeaways

  • =TRIM(text) removes the spaces at the start and end of a text and turns every run of spaces between words into one. Compare LEN before and after to see what it removed.
  • Wrap TRIM in PROPER, UPPER or LOWER to fix the capitals too. Give it a whole range in Microsoft 365 or Excel 2021 or later, and use Paste Special > Values to replace the original data.
  • In lookups and counts, trim the side that has the spaces: TRIM on the lookup value, TRIM on the range in XLOOKUP or MATCH, and SUMPRODUCT instead of COUNTIF.
  • When TRIM doesn't work, the spaces aren't regular ones. Swap CHAR(160) for a space with SUBSTITUTE, use CLEAN for line breaks, and convert numbers back with VALUE.

Practice

Keep going with TRIM and CLEAN exercises

Reading gets you started. Solving a few of these is what makes it stick.

  1. 1Clean up a nameFreeClean the name in A2: remove the extra spaces and capitalize it properly.→
  2. 2First name from a messy cell PremiumGet the first name from A5, cleaned up and capitalized: Noah.→
  3. 3Build a label PremiumFor row 5, build a label like AW-2024-1120 - Sofia Martinez: the order code, a space, a dash, a space, then the clean name.→
  4. 4Replace characters PremiumReformat the phone number in C3 from 555.388.1902 to 555-388-1902.→
  5. 5Count characters PremiumHow many characters are in the phone number in C5, including spaces and brackets?→
All text exercises →
Brian Millot

About the author

Brian Millot

Brian Millot is an indie hacker based in France who builds small, focused web products. He built and runs Excel Formula Practice, from the in-browser spreadsheet that checks your formulas to the exercise library, and he writes the formula guides.

Related guides

Practice makes formulas

Stop reading. Start typing formulas.

Every exercise is a real spreadsheet with instant feedback. The first two in every topic are free, no sign-up needed.