TRIM Generator
Remove leading, trailing, and doubled-up spaces from text in a cell.
TRIM
Remove leading, trailing, and doubled-up spaces from text in a cell.
The cell whose spacing you want cleaned up. Literal text needs quotes, for example " hello ".
=TRIM(A2)Worked Example
A2 contains the imported text " Ada Lovelace " with padding at both ends and three spaces in the middle.
=TRIM(A2)Returns: Returns Ada Lovelace: the outer spaces are gone and the internal run is reduced to a single space.
Checks Before You Paste
- •TRIM only collapses runs of spaces down to one and clears the ends. It never removes all internal spaces, so use =SUBSTITUTE(A2, " ", "") if you want none at all.
- •Text copied from web pages or PDFs often contains a non-breaking space, CHAR(160), which is not the plain CHAR(32) space TRIM looks for, so TRIM leaves it in place. Clean it first: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
- •TRIM always returns text, so a trimmed number stays left-aligned and is skipped by SUM, COUNT, and most lookups. Wrap it as =VALUE(TRIM(A2)) when the cell should behave as a number.
How TRIM works
Availability: TRIM has been in Excel since the early 1990s and works in every current version, in Excel for Mac and for the web, and in Google Sheets with identical behaviour. The companion functions used alongside it - SUBSTITUTE, CLEAN, CODE and VALUE - are equally universal, so a cleaning formula built here opens anywhere.
TRIM does two things to a piece of text: it deletes every space before the first character and after the last, and it reduces any run of spaces between words to a single space. " ACME Trading Ltd " becomes "ACME Trading Ltd". It is the standard first step on data pasted out of a report, a PDF, or another system, because trailing spaces are invisible on screen and quietly break the things that depend on exact matches.
That invisibility is the whole reason the function exists. A VLOOKUP for "INV-0042" fails against a stored "INV-0042 " with a #N/A, two columns that look identical compare as FALSE, and a PivotTable shows the same customer twice. None of it is visible in the cell, so the fault usually gets blamed on the lookup rather than the data.
TRIM only recognises the ordinary space, CHAR(32). Text copied from a web page very often contains the non-breaking space CHAR(160) instead, which renders identically and survives TRIM untouched. The reliable pattern is to convert first and trim second: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). Tabs, line feeds and other non-printing characters need CLEAN, which strips character codes 0 to 31, giving =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))) as the belt-and-braces version.
One consequence catches people out at the end: TRIM always returns text. Trim a column of numbers that arrived as text and you still have text - left-aligned, ignored by SUM, invisible to numeric lookups. Wrap the result in VALUE when the cell is supposed to be a number, and remember that TRIM on a genuinely empty cell returns an empty string, which is not the same as blank.
Syntax
=TRIM(text)| Argument | Required | What it does |
|---|---|---|
text | Required | The text to clean: a cell reference, literal text in double quotes, or another formula that returns text. Exactly one argument - TRIM does not take a range, so =TRIM(A2:A100) either intersects to a single row or spills one result per row in Microsoft 365. Numbers, dates and logical values are converted to text before trimming. |
More worked examples
A2 holds the pasted invoice reference "INV-0042 " with one trailing space, and the lookup table in $D$2:$E$50 holds the clean "INV-0042".
=VLOOKUP(TRIM(A2), $D$2:$E$50, 2, FALSE)Returns: The matching value from column E, instead of the #N/A the same lookup returns without TRIM.
Trimming inside the lookup avoids a helper column, but it only fixes the lookup value. If the trailing spaces are in the table itself, clean the table.
A2 holds a product name copied from a supplier's website: it displays as "Widget Blue" but LEN(A2) reports 13 rather than 11, because two of the characters are non-breaking spaces.
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))Returns: Widget Blue, with LEN now 11.
Plain =TRIM(A2) leaves this cell exactly as it was. SUBSTITUTE turns each CHAR(160) into a real space first, and only then does TRIM have something it recognises.
A2 holds " 1,240 " - a quantity exported as text with padding on both sides - and the column below it needs to sum.
=VALUE(TRIM(SUBSTITUTE(A2, ",", "")))Returns: 1240, as a real number that right-aligns and adds up.
TRIM alone returns the text "1,240", which SUM skips. Stripping the thousands separator matters in locales where VALUE will not parse it.
Common mistakes
- TRIM appears to do nothing on imported text
- The usual cause is CHAR(160), the non-breaking space used throughout HTML. Diagnose it with =CODE(RIGHT(A2,1)): 32 is a normal space TRIM would have removed, 160 is the one it ignores. Fix with =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). Character 127 and various Unicode spaces behave the same way and need the same treatment with their own CHAR or UNICHAR codes.
- Expecting TRIM to remove all internal spaces
- TRIM collapses "Widget Blue" to "Widget Blue", never to "WidgetBlue". If you want no spaces at all - matching part numbers, say - use =SUBSTITUTE(A2, " ", "") instead, which removes every one of them including the ones between words.
- Trimmed numbers stop summing
- TRIM returns text under all circumstances, so a column of =TRIM(A2) results left-aligns and SUM reports 0. Wrap it as =VALUE(TRIM(A2)), or paste the trimmed values back over the originals and run Text to Columns with Finish to force a re-parse.
- CLEAN does not remove everything non-printing
- CLEAN strips character codes 0 to 31 only. Higher-numbered oddities - CHAR(127), CHAR(160), zero-width spaces from web copy - pass straight through both CLEAN and TRIM. When a cell still misbehaves after both, run =CODE(MID(A2, n, 1)) across the positions to find the culprit and SUBSTITUTE it out by code.
- The trimmed result is an empty string, not a blank
- =TRIM(A2) on a cell containing only spaces returns "". That cell now reads as non-empty to ISBLANK and COUNTA even though it shows nothing. If your downstream count needs it treated as missing, test with =LEN(TRIM(A2))=0 rather than ISBLANK.
Frequently Asked Questions
Because the character is not a space in Excel's sense. Web pages and Word documents use the non-breaking space, CHAR(160), which draws identically but has a different character code, and TRIM only removes CHAR(32). Check with =CODE(RIGHT(A2,1)) - if it says 160, use =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
TRIM handles spaces: the ends of the string and repeated runs in the middle. CLEAN handles non-printing control characters, codes 0 through 31, which is what line feeds and tabs pasted from a terminal or a PDF turn into. They fix different problems, so imported data often needs both: =TRIM(CLEAN(A2)).
TRIM is a formula, so it needs to live somewhere. The usual route is a helper column of =TRIM(A2), then copy it, Paste Special as Values over the original column, and delete the helper. Find and Replace also works for the common case: replace two spaces with one and press Replace All repeatedly until it reports zero replacements, though that will not touch leading or trailing single spaces.
It fixes the version of that problem caused by stray spaces, which is a common one. Wrapping the lookup value as =VLOOKUP(TRIM(A2), ...) handles spaces on the search side. If the spaces are in the lookup table's first column, no amount of trimming the search value helps - clean the table, or switch to a formula that trims both sides such as an INDEX/MATCH over TRIM applied as an array.
It converts it to text. TRIM(A2) on a date cell returns the serial number as a text string, for example "46082" rather than 01/03/2026. Only apply TRIM to cells that really do hold text; if a column is a mix, =IF(ISTEXT(A2), TRIM(A2), A2) leaves the genuine numbers and dates alone.
Related Tools
Excel Data Cleaner
Trim spaces, drop duplicates and fill blanks across an uploaded workbook in one pass.
SUBSTITUTE Generator
Build the SUBSTITUTE call that removes non-breaking spaces before TRIM runs.
Convert Text to Number
Turn the text a trim leaves behind into real numeric cells.
LEFT RIGHT MID Generator
Pull fixed-position pieces out of text once the spacing is consistent.