In a Nutshell
TRIM is a Microsoft Excel function that removes all spaces from a string of text, except for single spaces between words.
The TRIM function is useful for cleaning up text imported into Excel that may have irregular spacing.
It is categorised in the Text Function Library on the Formulas tab:
Syntax
TRIM(text)
The TRIM function syntax requires only one argument:
- text – The text string to be cleaned up
Notes
- TRIM clears out leading spaces, trailing spaces and any double spaces found throughout the text string.
- TRIM only clears out the regular space character (ASCII value 32).
- Other spaces, e.g. non-breaking spaces (ASCII character 160) are not removed by TRIM and would need to be removed using other functions such as SUBSTITUTE.
Examples
Let’s take a look at a few examples of TRIM in action. The example file can be downloaded here.
Example 1 – Trimming of extra spaces
In the sentence below, we can see there are leading spaces, additional spaces throughout the text and some trailing spaces.
The extra spaces can be cleared with the TRIM function:
=TRIM(B3)
Example 2 – Trimming other “non-standard” spaces
In the sentence below, we can again see some extra space characters, but these are non-breaking spaces (ASCII code 160), and TRIM will not get rid of them:
Step 1 – To fix it, we first need to convert the non-breaking spaces to standard (ASCII code 32) spaces. We do this by using SUBSTITUTE and CHAR:
=SUBSTITUTE(B4, CHAR(160), CHAR(32))
At first it looks like the SUBSTITUTE formula in cell B7 did nothing, but in fact it swapped out all non-breaking spaces for normal spaces.
It says “take all CHAR(160)’s in cell B4 and swap them out for CHAR(32)’s”. More details on the Excel CHAR Function here.
Step 2 – We can now TRIM the output from Step 1 above:
The text in B7 (after Step 1) still has extra spaces, but they are now normal spaces. Step 2 performs a TRIM on the value in B7:
=TRIM(B7)
Example 3 – Trimming other “non-standard” spaces in one step
Instead of performing the SUBSTITUTE as one step and then the TRIM as another step, we can combine them into one formula:
So we build the SUBSTITUTE formula as before, but we then copy and paste it into the TRIM function in cell B21:
=TRIM( SUBSTITUTE(B4, CHAR(160), CHAR(32) ) )
In case you missed it, here is a link to the example workbook.
Errors
- TRIM rarely produces an error message (#N/A, #REF!, etc.) It is more likely that it just won’t work as intended, e.g. Example 2 above where the extra spaces aren’t normal spaces, but non-breaking spaces (ASCII code 160). These need to be swapped out for normal spaces first, use SUBSTITUTE.
-
A #REF! error is possible when the text that is being referred to by the function no longer exists. E.g. cell A1 has the formula =TRIM(B1), but then a value is cut and paste over the top of B1. In this case, the formula in A1 will change to =TRIM(#REF!) and the value in the cell will therefore also display #REF!
Excel Versions Supported
All versions