On this page ⌄
- The TRIM syntax
- A basic TRIM example
- Check for extra spaces with LEN
- Fix spacing and capitals: TRIM with PROPER, UPPER and LOWER
- TRIM a whole column at once
- Replace the original data with the trimmed text
- Use TRIM in XLOOKUP, INDEX MATCH and VLOOKUP
- Use TRIM with COUNTIF and UNIQUE
- TRIM not working? Remove non-breaking spaces
- Remove line breaks and hidden characters with CLEAN
- Remove all spaces with SUBSTITUTE
- Remove extra spaces without a formula
- TRIM returns text, not numbers
- Common TRIM mistakes
- Frequently asked questions about TRIM
- Which versions of Excel have TRIM?
- 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 name | B: Email | C: Phone | D: Order code | |
|---|---|---|---|---|
| 2 | emma JOHNSON | [email protected] | (555) 201-4433 | NW-2026-0187 |
| 3 | liam o'brien | [email protected] | 555.388.1902 | CT-2025-0942 |
| 4 | OLIVIA chen | [email protected] | 555-647-2210 | FB-2026-0033 |
| 5 | noah patel | [email protected] | (555) 903 7751 | NW-2026-0451 |
| 6 | Sofia Martinez | [email protected] | 555 412 0098 | AW-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
| Argument | What it means | Example |
|---|---|---|
| text | The text to remove spaces from: a cell, text in quotes, another formula, or a whole range in Microsoft 365 and Excel 2021 or later | A2 |
Here's what TRIM does with each kind of space:
| Where the spaces are | What TRIM does |
|---|---|
| Before the first word | Removes all of them |
| After the last word | Removes all of them |
| Two or more between words | Keeps one |
| One between words | Keeps it |
| Nothing but spaces in the cell | Returns empty text |
So TRIM tidies text you type in quotes too:
=TRIM(" monthly sales report ")→ monthly sales reportTRIM 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 patelThe three spaces between the words become one. TRIM removes the two spaces at the end of A4 the same way:
=TRIM(A4)→ OLIVIA chenThat 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))→ 5Here are all five names, with each space shown as a dot:
| A: Full name | LEN | After TRIM | LEN after TRIM | |
|---|---|---|---|---|
| 2 | ··emma···JOHNSON· | 17 | emma·JOHNSON | 12 |
| 3 | liam·o'brien | 12 | liam·o'brien | 12 |
| 4 | OLIVIA·chen·· | 13 | OLIVIA·chen | 11 |
| 5 | noah···patel | 12 | noah·patel | 10 |
| 6 | Sofia·Martinez | 14 | Sofia·Martinez | 14 |
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)→ TRUEHow many names in the column need cleaning?
=SUMPRODUCT(--(A2:A6<>TRIM(A2:A6)))→ 3The 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 ChenUPPER and LOWER work the same way, for all capitals or none:
=UPPER(TRIM(A5))→ NOAH PATEL=LOWER(TRIM(A4))→ olivia chenThe 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'BrienThe 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.
Clean the name in A2: remove the extra spaces and capitalize it properly.
Write your formula in the green cell, G2.
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 MartinezThe 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:
- Put the formula in an empty column, such as
=TRIM(A2:A6)in F2, or=TRIM(A2)copied down to F6. - Select F2:F6 and copy it with Ctrl+C.
- Right-click A2 and choose Paste Special > Values, or press Ctrl+Alt+V, then V, then Enter.
- 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-0451noah 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-0942You 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))→ 6COUNTIF 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"))→ 2A wildcard works too. * stands for any characters, spaces included, so this matches both rows:
=COUNTIF(A2:A7, "noah*patel")→ 2It 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)))→ 5UNIQUE 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))→ 15TRIM 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))→ 32160 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→ TRUECHAR(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)))→ 14Put 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)9037751That'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, " ", ""))→ 6Like 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)→ 250The 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
| Problem | What's going on | Fix |
|---|---|---|
| TRIM leaves a space | It's a non-breaking space, character 160, usually from a web page | =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) |
| Text still breaks onto two lines | A line break isn't a space | CLEAN, or SUBSTITUTE CHAR(10) with a space |
| Words run together, as in noahpatel | SUBSTITUTE, Find & Replace or CLEAN removed the space between words | Use TRIM for extra spaces, and replace line breaks with a space |
| Circular reference warning | TRIM points at its own cell, as in =TRIM(A2) typed in A2 | Put the formula in another column |
#REF! after cleaning the data | The formulas were pasted instead of their values, or the column they point at was deleted | Use Paste Special > Values before deleting anything |
A lookup still returns #N/A | Only one side is trimmed, or the spaces are non-breaking | TRIM the lookup value and the range, and check for CHAR(160) |
| Excel won't accept COUNTIF with TRIM | COUNTIF's range must be cells, not a calculated list | Use SUMPRODUCT with TRIM instead |
| A number won't add up or match | TRIM returns text | Wrap it in VALUE, or put -- in front |
| A date turns into a 5-digit number | TRIM returns the date's serial number as text | Don't TRIM dates or numbers |
#SPILL! | =TRIM(A2:A6) needs empty cells below it | Clear 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)=""→ TRUETo 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.
- 1Clean up a nameFreeClean the name in A2: remove the extra spaces and capitalize it properly.→
- 2First name from a messy cell PremiumGet the first name from A5, cleaned up and capitalized: Noah.→
- 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.→
- 4Replace characters PremiumReformat the phone number in C3 from 555.388.1902 to 555-388-1902.→
- 5Count characters PremiumHow many characters are in the phone number in C5, including spaces and brackets?→

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.