My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel Formulas / Excel TRIM Function Removes Spaces From Text
Excel TRIM Function Removes Spaces From Text
by

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.

excel trim function example

 
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   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:

excel substitute function example

 
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.

Related Tutorials

SUBSTITUTE Function - replaces text with another.

Excel TRIM Function Removes Spaces From Text

More Text Formulas Posts

Excel TEXT Function – handy but limited…or is it?
The Excel TEXT Function converts numbers to text in the format you specify. It's hand for joining numbers and text together in custom chart labels etc.
Extract Text from a Cell using MID Formulas
Excel Test if a Range Contains Text, Numbers or is Empty
Excel CLEAN Formula
The Excel CLEAN Function can help you to remove unwanted characters that are often imported when you copy data from web pages, but it has some limitations
Excel SUBSTITUTE Formula
The Excel SUBSTITUTE function replaces new text for old in a text string. It's an alternative to using Find and Replace by retaining the original data.
Excel Factor Entry 3 Re-format Data Using Formulas
Use Excel's Text functions to re-format data imported from external sources
Excel SEARCH and You Will FIND
Excel SUBSTITUTE Function Trick
Excel UPPER LOWER and PROPER Functions
Microsoft Excel’s T Function
The Excel T function checks whether a value is text, and returns the text if it is, or returns double quotes (empty text) if it isn’t.

More Excel Formulas Posts

top excel functions for data analysts
Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment
Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips
Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays
The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do
Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function
The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files
New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel
How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions
TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.
Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Tag:

Reader Interactions

Comments

  5. Tom Black

    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

    Reply

    • Mynda Treacy

      Hi Tom,

      TRIM will remove spaces in front and at the end, but I’m not sure what you can do to keep the spaces at the front other than adding them back in again with CONCATENATE after using TRIM.

      CONCATENATE will work if you need the same number of spaces at the front of every cell.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply

  6. Kevin

    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.

    Reply

  7. Prana Dey

    From past two days i was trying to resolve the issue of Character 160 and today your online training helped me to resolve it. I just appreciate your efforts. You are just wonderful.

    Thank you so so much

    Reply

  8. Alwi

    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.

    Reply

  9. richard

    please help,

    i want to separate or delete the character enclose in the ()
    Fundamentals of Accounting (ACCTG 1)
    Partnership and Corporation (ACCTG 2)
    Financial Accounting Problems 1 (ACCTG 21)
    Financial Accounting Problems 2 (ACCTG 22)
    Accounting (ACCTG(ECON) 1)
    Basic Mechanical Engineering (ACE 01)

    thank you very much

    Reply

    • Mynda Treacy

      Hi Richard,

      There’s a few ways to do this but one of the simplest ways to get rid of the () is to use ‘Find and Replace’. To open the Find and Replace dialog box press CTRL+H.

      You might come unstuck on Accounting as it has two sets of ().

      Alternatively you could use Text to Columns to separate the text or the SUBSTITUTE Function.

      Hopefully one of those options is helpful to you.

      Kind regards,

      Mynda.

      Reply

  10. paul

    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”>’.

    This is typical of the links – 

    <a href="AgathaChristie/14 - Miss Marple's Final Cases.pdf" rel="nofollow">Miss Marple's Final Cases.pdf</a>
    <a href="AgathaChristie/11 - Three Act Tragedy.pdf" rel="nofollow">Three Act Tragedy.pdf

    Well, is it possible?
    All the best
    paul

    Reply

    • Mynda Treacy

      Hi Paul,

      Thanks for your question. I use Excel all the time to update and edit HTML so I may be able to help you 🙂

      1. The first thing I would do is use Text to Columns to extract the book names into a separate column using the greater than sign as the delimiter.

      So you will end up with two columns:

      Column A contains:

      <a href="AgathaChristie/14 – Miss Marple's Final Cases.pdf" rel="nofollow"

      <a href="AgathaChristie/11 – Three Act Tragedy.pdf" rel="nofollow"

      Column B contains:

      Miss Marple's Final Cases.pdf</a

      Three Act Tragedy.pdf

      CTRL+H to open Find & Replace dialog box > in the Find cell type a space > leave the Replace cell empty. Click ‘Replace All’.

      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

      5. Copy and paste column C as values.

      Job done.

      Let me know how you get on. If you get stuck send me your file.

      Cheers,

      Mynda.

      Reply

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *