Excel Tutorials · Lesson 51
A trailing space you cannot see will break a lookup, split one category into two in a PivotTable, and make two identical-looking values fail an equals test. TRIM removes every stray space in one pass. This lesson covers the function, the one type of space it cannot remove, and how to turn the cleaned results back into permanent values.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
TRIM removes all spaces from a text string except for single spaces between words. The syntax could not be simpler: =TRIM(A2). Leading spaces go, trailing spaces go, and any run of multiple spaces in the middle collapses down to one. What it deliberately leaves alone is the single space that separates words, because that is legitimate text rather than noise.
The reason this matters is that Excel compares text exactly. "Catering" and "Catering " with one trailing space are two different values as far as every function is concerned. A VLOOKUP returns #N/A. A PivotTable creates two separate rows for what is obviously one category. COUNTIF misses half the matches. An IF test comparing them returns FALSE. None of these failures announce themselves, and none of them look like a spacing problem, which is why they burn so much time.
Stray spaces arrive almost entirely from outside Excel. Data pasted from a website, exported from an accounting or CRM system, converted from a PDF, or typed by someone who hit the space bar once more than intended. The spaces are genuinely there in the cell, they are simply invisible against a white background.
TRIM has one important blind spot. It only removes the standard space character, ASCII 32. Web-sourced data frequently contains a non-breaking space, character 160, which looks identical on screen and which TRIM will not touch. When a cell still fails a comparison after TRIM has run, character 160 is almost always the reason, and the fix is SUBSTITUTE.
Step-by-step, matching the video above.
In a spare cell, use =LEN(A2) to count the characters. If the count is higher than the visible text, there are hidden spaces. Comparing =LEN(A2)-LEN(TRIM(A2)) tells you exactly how many.
TRIM cannot clean a cell in place, it has to write its result somewhere else. Right-click the column header next to your data and choose Insert to make room.
In the first cell of the helper column, type =TRIM(A2), pointing at the first cell of the messy data, and press Enter.
Double-click the small square at the bottom-right corner of the cell to copy the formula down as far as your data runs, or select the range and press Ctrl+D.
Run =LEN() against the cleaned column. The character counts should now match the visible text, and any value that still looks wrong is a candidate for the non-breaking space fix below.
Select the helper column, copy it, then right-click and choose Paste Special > Values. This is essential, the formulas depend on the original column and will break the moment you delete it.
With the cleaned data now stored as permanent values rather than formulas, remove the original messy column and rename the header. The cleanup is finished.
| A: Original | B: LEN(A2) | C: =TRIM(A2) | D: LEN(C2) |
|---|---|---|---|
| Acme Corp | 11 | Acme Corp | 9 |
| Acme Corp | 12 | Acme Corp | 9 |
| Acme Corp | 13 | Acme Corp | 9 |
| Acme Corp | 15 | Acme Corp | 9 |
Four cells that all display as roughly the same company name, with four different character counts, meaning four different values to every formula in the workbook. TRIM collapses all of them to the same nine-character string. Without it, a lookup matching on company name finds one of these four and fails on the rest.
Four cleanup functions, and the order to apply them in.
Character 160 survives TRIM entirely. Nest them: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) converts every non-breaking space into a normal one first, then lets TRIM clean up the result. This is the single most useful formula on this page for web-sourced data.
Data from a PDF or a text export often carries invisible line breaks. =CLEAN(A2) strips the first 32 non-printing characters, and =TRIM(CLEAN(A2)) handles both problems in one pass.
For genuinely messy imports, combine all three: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))). It handles non-breaking spaces, control characters and stray spacing together, and it is worth keeping on hand.
Press Ctrl+H, type a single space in Find, leave Replace empty, and click Replace All to strip every space including the ones between words. Use this only on data with no legitimate internal spaces, such as ID codes or part numbers.
In Find and Replace, put two spaces in Find and one in Replace, then click Replace All repeatedly until the count comes back zero. It collapses runs of spaces in place, though it will not touch leading or trailing ones.
How to prove that spacing is the culprit before you start fixing anything.
=LEN(A2)-LEN(TRIM(A2)) returns the number of removable spaces. Anything above zero confirms the problem and tells you how bad it is, row by row.
=CODE(RIGHT(A2,1)) returns the character code of the last character. A 32 means a regular trailing space, a 160 means a non-breaking space that needs SUBSTITUTE, and anything else means the text really does end there.
="["&A2&"]" wraps the value in brackets so any leading or trailing space becomes immediately visible on screen, which is the fastest way to show someone else what is wrong.
Numbers stored correctly right-align by default. A column of numbers sitting on the left is text, often because a trailing space forced Excel to treat the whole value as a string.
=A2=B2 returning FALSE on two values that look identical is proof enough. Wrap both in TRIM and retest, and if it flips to TRUE you have found the cause.
| Lookup Value | Table Value | Raw Match | Match After TRIM |
|---|---|---|---|
| Northeast | Northeast | #N/A | Found |
| Midwest | Midwest | #N/A | Found |
| South West | South West | #N/A | Found |
Three lookups that appear correct on screen and return #N/A anyway. Wrapping the lookup value in TRIM, as in =VLOOKUP(TRIM(A2),Table,2,FALSE), fixes all three without touching the source data. When a lookup fails on a value you can plainly see in the table, spacing is the first thing to rule out.
Better options once you are cleaning the same import every week.
Under Data > Get & Transform, right-click a column and choose Transform > Trim. The step is saved with the query, so every future refresh of that data source cleans itself with no formulas at all.
Splitting on a delimiter with Data > Text to Columns naturally drops surrounding spaces on many imports, which can solve the problem as a side effect of work you were already doing.
Trim before creating a PivotTable, not after. A PivotTable built on untrimmed data splits one category into several rows, and every summary figure downstream is quietly wrong.
For data people type by hand, a drop-down list built with data validation removes the possibility entirely, because nobody is typing the value and so nobody can add a space to it.
Identical function and identical behavior, and CLEAN and SUBSTITUTE both exist there too. Sheets adds a menu shortcut under Data > Data cleanup > Trim whitespace that cleans a selection in place with no helper column at all.
The TRIM results are formulas pointing at the original cells. Delete that column first and the whole helper column collapses to #REF!. Always Paste Special as Values before removing anything.
TRIM only handles the standard space, character 32. Non-breaking spaces from web data survive it completely, and they need SUBSTITUTE(A2,CHAR(160)," ") first.
Replacing every space with nothing turns "Acme Corp" into "AcmeCorp". That approach only suits data with no legitimate internal spaces, like codes and IDs.
A summary built on untrimmed data has already split categories apart and totaled them separately. Clean the source first, then build, or refresh and check every category row.
Running TRIM on a numeric column returns text that looks like numbers but will not sum. Wrap the result in VALUE, or use =VALUE(TRIM(A2)), to convert it back.
If the messy data arrives on a schedule, a Power Query trim step runs automatically on every refresh and removes the manual pass entirely.
Data shouldn’t need scrubbing before anyone can trust it.
Free 14-day trial. No credit card required.
Use =TRIM(A2) in a helper column, fill it down, then copy the results and use Paste Special > Values before deleting the original column.
Leading spaces, trailing spaces, and any run of multiple spaces between words, which it collapses to a single space. It deliberately keeps single spaces between words.
It is probably a non-breaking space, character 160, which is common in data copied from websites. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) to convert it first.
Compare =LEN(A2) against =LEN(TRIM(A2)). The difference is the number of removable spaces, and anything above zero confirms the problem.
No, TRIM writes its result to a different cell. Use a helper column and then Paste Special as Values over the original, or use Find and Replace for an in-place fix.
TRIM removes extra spaces, while CLEAN removes non-printing control characters such as line breaks. Combine them as =TRIM(CLEAN(A2)) for messy imports.
A trailing space on either the lookup value or the table value makes them different strings. Wrap the lookup value in TRIM, as in =VLOOKUP(TRIM(A2),Table,2,FALSE).
Use Find and Replace with a single space in Find and nothing in Replace, or =SUBSTITUTE(A2," ",""). Only do this on codes and IDs with no real internal spaces.
TRIM always returns a text string. Wrap it in VALUE, as in =VALUE(TRIM(A2)), to convert the cleaned result back into a number that will sum.
Use Power Query under Data > Get & Transform, right-click the column and choose Transform > Trim. The step is saved and reruns on every refresh.
Yes, that split is caused by inconsistent spacing making them different values. Trim the source data first, then rebuild or refresh the PivotTable.
Use =CODE(RIGHT(A2,1)) to see the last character's code. 32 is a normal space, 160 is a non-breaking space, and anything else means the text genuinely ends there.
Yes, identically, and Sheets also offers a menu shortcut under Data > Data cleanup > Trim whitespace that cleans a selection in place without a helper column.
For hand-entered data, use a data validation drop-down list so values are selected rather than typed, which removes the opportunity to add a stray space.
Every business starts with a spreadsheet. Updoot is where you scale past it, with structured data that arrives clean the first time.
Start Your Free Trial →