Excel TRIM Function
last modified October 5, 2026
The TRIM function is an essential text manipulation tool in Excel.
It removes leading and trailing spaces from text and collapses every run of
internal spaces to a single space, so only single spaces remain between words.
This tutorial provides a comprehensive guide to using the TRIM
function with detailed examples. You'll learn its basic syntax, the
internal-space behavior, practical applications, and how to combine it with
other functions to clean text data in Excel.
TRIM Function Basics
The TRIM function removes the leading and trailing spaces from text
and reduces any sequence of internal spaces to one space. It's particularly
useful for cleaning data imported from other systems. The syntax is simple but
powerful for text processing.
| Component | Description |
|---|---|
| Function Name | TRIM |
| Syntax | =TRIM(text) |
| Arguments | text - The text to clean |
| Return Value | Text with normalized spacing |
This table breaks down the essential components of the TRIM
function. It shows the function name, basic syntax format, required argument,
and return value characteristics.
| Argument | Meaning |
|---|---|
| text | The text to clean. It can be a literal string in quotes, a cell
reference, or an expression that returns text, such as
A1&B1. Numbers are coerced to text automatically. |
TRIM takes exactly one argument. Supplying more than one argument
produces a "too many arguments" error. The function always returns text, so
=TRIM(42) returns the text "42" rather than the number 42. Only the
regular space character, CHAR(32), is affected; tabs, line breaks,
and the non-breaking space are left in place.
How TRIM Collapses Internal Spaces
The behavior that sets TRIM apart from simply deleting the ends of
a string is that it also rewrites the inside of the text. Every run of two or
more regular spaces between words becomes a single space. Single spaces are left
alone, and a string made only of spaces becomes empty text.
| Formula | Result |
|---|---|
| =TRIM(" hello world ") | hello world |
| =TRIM("a b c") | a b c |
| =TRIM("hello") | hello |
| =TRIM(" ") | (empty text) |
The first two rows show the collapsing rule, the third row shows that text
without extra spaces is returned unchanged, and the last row shows that a
string of spaces alone disappears entirely. This is why TRIM is so
effective on data copied from web pages or exported from other systems.
Basic TRIM Example
This example demonstrates the simplest use of the TRIM function with a text string containing extra spaces and how it normalizes the result.
=TRIM(" Excel TRIM function ")
This formula processes the text string, removing leading, trailing, and redundant spaces. The result will be "Excel TRIM function" with single spaces.
TRIM with Cell References
A more practical use of TRIM involves cleaning text from specific
cells. Here's an example with cell references.
| A | B |
|---|---|
| Data cleaning | |
| =TRIM(A1) |
The table shows a simple spreadsheet with text containing extra spaces in cell
A1 and a TRIM formula in cell B1 that cleans the text.
=TRIM(A1)
This formula processes the text in cell A1, removing all leading, trailing, and multiple internal spaces. The result will be "Data cleaning".
TRIM and the Non-Breaking Space
TRIM removes only the regular space character,
CHAR(32). It does not remove the non-breaking space,
CHAR(160), even though the two look identical on screen.
Non-breaking spaces are common in text copied from web pages, and they are the
usual reason why cleaned text still looks as if it contains extra spaces.
Because CHAR(160) is a printable character, CLEAN
cannot remove it either, so the standard recipe converts it to a normal space
with SUBSTITUTE before trimming.
| A | B |
|---|---|
| Report Data | |
| =TRIM(SUBSTITUTE(A1,CHAR(160)," ")) |
In the table, cell A1 contains two non-breaking spaces between the words. The
formula in B1 converts each of them to a regular space with
SUBSTITUTE, and then TRIM normalizes the result.
=TRIM(SUBSTITUTE(A1,CHAR(160)," "))
=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
This formula first replaces non-breaking spaces with regular spaces using SUBSTITUTE, then applies TRIM. For the sample text with two non-breaking spaces, it returns "Report Data" with a single regular space.
When the text may also contain control characters such as tabs or line breaks,
add CLEAN to the recipe. The full non-breaking space recipe shown
above converts the non-breaking spaces, strips the control characters, and then
trims the leftover spaces in one pass.
TRIM vs CLEAN
TRIM and CLEAN are often used together, but they solve
different problems. TRIM manages ordinary spaces, while
CLEAN removes the non-printing ASCII control characters. Neither
one touches the non-breaking space, which is why SUBSTITUTE is
needed for that character.
| Character | TRIM | CLEAN |
|---|---|---|
| Leading and trailing spaces | Removes | Keeps |
| Runs of internal spaces | Collapses to one | Keeps |
| ASCII control codes 0-31 (tab, line feed) | Keeps | Removes |
| Non-breaking space CHAR(160) | Keeps | Keeps |
Read the table column by column: TRIM is the space specialist, and
CLEAN is the control-character specialist. To handle everything,
chain them as =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))).
TRIM with Concatenated Text
When combining text from multiple sources, TRIM helps clean the results. This example shows TRIM with CONCATENATE.
| A | B | C |
|---|---|---|
| John | Doe | |
| =TRIM(CONCATENATE(A1, " ", B1)) |
The table illustrates how TRIM can clean up text combined from multiple cells that may contain extra spaces. The CONCATENATE joins first and last names.
=TRIM(CONCATENATE(A1, " ", B1))
This formula combines first and last names with a space, then trims the result. Without TRIM, the joined text would be "John Doe " with four spaces between the names and two trailing spaces; with TRIM it becomes the single, clean "John Doe".
TRIM in Data Validation
TRIM is useful in data validation to ensure consistent text entry. This example shows a validation rule using TRIM.
=LEN(TRIM(A1))=LEN(A1)
This validation formula ensures entered text contains no leading, trailing, or multiple spaces. It compares the length of original and trimmed text.
TRIM with Other Functions
TRIM often works with other text functions for comprehensive cleaning. This example combines TRIM with PROPER for formatted names.
| A | B |
|---|---|
| john SMITH | |
| =PROPER(TRIM(A1)) |
The table shows how to combine TRIM with PROPER to clean and format names consistently. TRIM removes extra spaces before PROPER capitalizes the text.
=PROPER(TRIM(A1))
This formula first removes extra spaces with TRIM, then applies PROPER to format the name correctly. The result is "John Smith" from the messy input.
TRIM Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Text still looks unevenly spaced after TRIM | Apparent extra spaces remain | The characters are usually non-breaking spaces; remove them with
=TRIM(SUBSTITUTE(A1,CHAR(160)," ")) |
| Tab or line break inside the text | Words or lines stay separated | Strip them with CLEAN or
=SUBSTITUTE(A1,CHAR(9)," ") |
| More than one argument supplied | Too many arguments error | TRIM accepts a single text argument |
| Cell contains a number | Result is text, not a number | This is expected; apply VALUE to the result for arithmetic |
| Argument is an error value | The same error is passed through | TRIM cannot mask #N/A, #DIV/0!, or similar errors |
| Function name misspelled | #NAME? error | Type =TRIM(...) exactly |
The TRIM function is essential for cleaning and standardizing text
data in Excel. It removes leading and trailing spaces and collapses every run
of internal spaces to a single space, which makes imported and pasted text
predictable for lookups, comparisons, and sorting. Its one blind spot is the
non-breaking space, CHAR(160), which it leaves untouched; pair it
with SUBSTITUTE for that character and with CLEAN for
control characters. Mastering these combinations will significantly improve
your text processing capabilities.
Author
List all Excel Formulas.