Arguments
text- The text to clean.
TRIM
Removes leading, trailing, and repeated internal spaces from text.
Syntax
=TRIM(text)=TRIM(A2)Returns: A2 with extra spaces collapsed to single spaces
The fix for lookups that fail because of trailing spaces.
How TRIM evaluates
TRIM removes spaces from text: every leading space, every trailing space, and any run of internal spaces reduced to a single one. The internal collapsing is the part people forget - TRIM("John Smith") is "John Smith", not "John Smith" with only the ends tidied. That behaviour is deliberate and dates back to TRIM's original purpose, cleaning up text copied out of fixed-width mainframe reports where columns were padded with spaces.
The critical limitation is that TRIM only knows one character: the ordinary space, ASCII 32. Data pasted from a web page, a PDF or an HTML email is usually padded with the non-breaking space, CHAR(160), which looks identical on screen and which TRIM leaves completely alone. That is why a lookup can still fail after you have trimmed the column, and why =LEN(A2) can report more characters than you can count. Tabs (CHAR(9)), line feeds (CHAR(10)) and zero-width spaces from web copy are equally invisible to TRIM. The standard cleanup for imported text is therefore =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))): SUBSTITUTE converts the non-breaking spaces into real ones, CLEAN strips the control characters, and TRIM finishes the job.
TRIM always returns text. Passing it a number gives you the digits back as a string, so =TRIM(A2) on a numeric column produces values that will no longer sum - use VALUE(TRIM(A2)) when the column is meant to stay numeric. And because TRIM is a formula, the cleaned values live in a new column; to clean in place you copy the results and paste them back as values, or do the whole job in Power Query, where Trim and Clean are single transformation steps.
- Which character it removes
- ASCII space, character 32, and nothing else. CHAR(160), tabs, line breaks and zero-width characters all survive TRIM untouched.
- Internal spaces
- Runs of two or more become one. Single spaces between words are always kept, so TRIM can never join two words together.
- Numbers and dates
- Converted to text first. =TRIM(1250) returns the text "1250", and a date cell returns its serial number as text - the number format is lost.
- Blanks
- An empty cell returns an empty string, and a cell containing only spaces returns an empty string too. Neither is a genuine blank afterwards, so ISBLANK on the result is FALSE.
- Errors
- An error value in the argument is returned as-is. TRIM cannot repair or hide an error.
- Case and content
- TRIM changes nothing except whitespace - it does not change case, punctuation or accented characters. Pair it with UPPER, LOWER or PROPER when case is also inconsistent.
Errors TRIM returns
| Error | What it means and how to fix it |
|---|---|
| #VALUE! | TRIM was given a range rather than a single value, as in =TRIM(A2:A50) in a version without dynamic arrays. Enter it one row at a time, or use Excel 365 where the range spills. |
| #N/A or another propagated error | The referenced cell already holds that error - a lookup that failed, for instance. TRIM passes it through. Wrap the source in IFERROR before cleaning. |
| #NAME? | A misspelling, or a nested function that does not exist in this version, such as TEXTBEFORE inside the TRIM in Excel 2019 or earlier. |
| #REF! | The cell being cleaned was deleted. Re-point the reference; the cleanup logic itself is unaffected. |
More examples
=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))Returns: Text cleaned of web-paste padding
The full cleanup for HTML-sourced data. SUBSTITUTE turns non-breaking spaces into ordinary ones so TRIM can see them; CLEAN removes control characters that CHAR(160) is often mixed with.
=LEN(A2)-LEN(TRIM(A2))Returns: How many spaces TRIM would remove
A diagnostic rather than a cleanup. If this returns 0 while the values still look padded, the padding is not ASCII spaces and needs SUBSTITUTE.
=VALUE(TRIM(A2))Returns: A padded number converted back to a real number
TRIM alone returns text that will not sum. VALUE restores it, and returns #VALUE! on any row where the cleaned text is not numeric - which is a useful way to spot bad rows.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Supported and unchanged. Power Query, available as an add-in for 2010 and 2013 and built in from 2016, offers Trim and Clean as transformation steps. |
| Excel 2019 | Identical behaviour. |
| Excel 2021 / Microsoft 365 | Identical, but now spills: =TRIM(A2:A100) in one cell cleans the whole column at once. |
| Google Sheets | Same rules and the same blind spot for CHAR(160). Sheets also has Data > Data cleanup > Trim whitespace, which edits the cells in place. |
| LibreOffice Calc | Supported with the same behaviour on ASCII spaces; CHAR(160) likewise survives, so the SUBSTITUTE step is needed there too. |
Does TRIM Work in Google Sheets?
Yes. TRIM uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements TRIM as a single-argument function that collapses runs of whitespace to one character and removes it from both ends of the text.
It is deliberately more aggressive than Excel here: the editor's TRIM also collapses tabs, line breaks and non-breaking spaces such as CHAR(160), where Excel's TRIM only ever touches the ordinary ASCII space. Text cleaned in the editor can therefore be shorter than the same text cleaned in Excel.
The argument is converted to text before cleaning, so a number returns its digits as text and an error value in the argument is returned unchanged.
CLEAN is implemented alongside it and strips characters below code 32 as well as character 127, and SUBSTITUTE, LEN, CODE and EXACT are available for tracking down whatever whitespace is left.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
TRIM questions
Something that is not an ASCII space. Check with =CODE(RIGHT(A2, 1)): 32 is a normal space, 160 is a non-breaking space from web or PDF copy, 9 is a tab, 10 is a line feed and 63 usually means a character that failed to import. Handle 160 with SUBSTITUTE(A2, CHAR(160), "") and codes below 32 with CLEAN. Also make sure both sides of the lookup were cleaned - trimming only the lookup value leaves the table just as mismatched.
Because collapsing internal runs is what it is for. There is no argument to switch that off. If your text uses multiple spaces meaningfully - ASCII alignment, indentation, code samples - do not use TRIM. Strip only the ends with a SUBSTITUTE-based formula, or use Power Query's Trim step, which also removes only leading and trailing whitespace.
No. That break is CHAR(10) and TRIM ignores it entirely - the cell will still be two lines tall afterwards. Remove it with =SUBSTITUTE(A2, CHAR(10), " ") or with CLEAN, which strips every character below code 32, then TRIM the result to tidy the spaces the removal leaves behind.
Enter the TRIM formulas in a spare column, select them, copy, then use Paste Special > Values over the original column and delete the helper. Doing it in the other order deletes the cells the formulas depend on and leaves #REF! behind. For a file you clean repeatedly, Power Query is better: its Trim and Clean steps re-run on every refresh.