My Online Training Hub

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

Excel TRIM Function Removes Spaces From Text

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

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

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

Excel SUMPRODUCT Function
The Excel SUMPRODUCT function is one of Excel's most versatile and underutilized functions allowing for flexible aggregations of data.
Summarize Months to Quarters
3 ways (good, better, best) to summarize monthly data into quarters using formulas. Lots of examples and sample file to download.
Excel BYROW and BYCOL Functions
Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows.
python in excel natively
How to use Python in Excel natively using libraries like Pandas, NumPy, Matplotlib, Seaborn and more for analysis and spectacular charts!
excel dynamic named ranges
Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas and PivotTables etc.
functions for financial modelling
Top 23 must know Excel functions for Financial Modeling. Includes example Excel file and step by step instructions.
excel formula by example
Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor
Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
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 Labs is a long awaited, new improved way to write, name and store Excel formulas, including LAMBDAS with the help of AI.


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 *