If you import data from another program it often comes in with additional spaces either at the beginning, end or sometimes between words.
Thankfully the TRIM function will remove the excess spaces from text, except for single spaces between words.
How to use the TRIM function
It’s a simple function
Where ‘text’ is the cell containing the text you want the additional spaces removed.
Examples of using the TRIM function
The image below shows three different scenarios in column B and the result of using the TRIM function in column C.
Note: in cell B5 I have highlighted the additional spaces at the end.
The formula in cell C4 is simply =TRIM(B4) and so on for rows 5 and 6.
Limitations of the TRIM Function
If you copy text from web pages and paste it into Excel the TRIM function will not remove non-breaking space characters commonly used in HTML web pages.
If you know HTML then you will recognise the non-breaking space as but when you copy it into Excel it simply appears as a space between your text.
If you need to remove this type of character or other non-printing characters you will need to use a combination of the CHAR function and the SUBSTITUTE function as follows:
Let’s say in cell A13 we have the following text with a non-breaking space at the beginning:
First some background as to how Excel interprets the non-breaking space.
Every character in Excel is given an ASCII value. The ASCII value for a non-breaking space is 160. But since the TRIM function cannot remove a non-breaking space we need to first convert it to a regular space that the TRIM function can deal with.
The ASCII code for a regular space is 32. So, that said our formula will be:
The formula reads:
SUBSTITUTE the non-breaking space character, (CHAR(160)), with the regular space character (CHAR(32)), then TRIM the space from cell A13.
Got a Text problem in Excel? Post your question in the comments below and I’ll see if I can help.