Excel TRIM Function Removes Spaces From Text

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 Excel TRIM function will remove the excess spaces from text, except for single spaces between words.

How to use the Excel TRIM function

It’s a simple function

=TRIM(text)

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.

Excel TRIM Function Limitations

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 &nbsp; but when you copy it into Excel it simply appears as a space between your text.

Remove this type of character or other non-printing characters with 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:

=TRIM(SUBSTITUTE(A13,CHAR(160),CHAR(32)))

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.

I need to remove spaces from only the end of the content in the cell. There used to be a formula RTRIM – I think it was an Excel formula, but I go between several programs and sometimes the formulas run together on me. the spaces at the left and the spaces between words/before the last letter of the last word, need to stay as is.
Any advice?
Thanks

I have a data base which is very big. One column of text entries is aligned to the left. One third of these entries have a space at the start. I want to remove this space so the data will filter properly. I have tried all your methods above without success.

Please help me to extract all characters (alpha and numeric) up to the colon but without the “:” sign in the following. I have more than 13,000 rows to extract.
00276A: Maintain on-going professional and personal development
0044X: Imported unit 1 – lifestyle & leisure
AURV281208A: Carry out brazing procedures
Thank you.

Here’s a challenge for you, Mynda. I wonder if even a genius like you can answer it?
On my free ebook website, eBookTrove.com (absolutely free, in the spirit of the web) I need to remove the spaces from the links to the ebooks, but the space in ‘a href=’ of course, has to stay there. And then I’d like to arrange them in alphabetical order. That’s for the titles straight after ‘” rel=”nofollow”>’.

I know this gets rid of all the spaces, even the ones you want to keep, but all you have to do then is put the spaces back in that you want. e.g. to fix a href, do a Find on ‘ahref’ and replace with ‘a href’, likewise for ‘.pdf”rel=nofollow”‘, replace with ‘.pdf” rel=nofollow”>’.

Don’t forget to do Column B to put the greater than sign back in for the the </a

So now column A & B are fixed.

3. Sort column A and B choosing 'Sort by' Column B.

Now your data is sorted by the book name.

4. In column C join the two columns back together using the ampersand. =A1&B1

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?