• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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
June 1, 2011 by Mynda Treacy

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?

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

Extract Text from a Cell using MID Formulas

Excel Test if a Range Contains Text, Numbers or is Empty

Excel Test if a Range Contains Text, Numbers or is Empty

Excel CLEAN Formula

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

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

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 SEARCH and You Will FIND

Excel SUBSTITUTE Function Trick

Excel SUBSTITUTE Function Trick

Excel UPPER LOWER and PROPER Functions

Excel UPPER LOWER and PROPER Functions

Microsoft Excel’s T Function

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

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

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

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

New Array Shaping Excel Functions

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

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

Excel VSTACK and HSTACK Functions

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

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

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

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: Excel FormulasTag: text formulas
Previous Post:Excel UPPER LOWER and PROPER FunctionsExcel UPPER LOWER and PROPER Functions
Next Post:Importing Data into ExcelImporting Data into Excel

Reader Interactions

Comments

  1. rajesh

    July 18, 2020 at 5:07 pm

    You saved me a lot. No where found the below info.
    =TRIM(SUBSTITUTE(A13,CHAR(160),CHAR(32)))

    Reply
    • Mynda Treacy

      July 18, 2020 at 5:15 pm

      So pleased it was helpful 🙂

      Reply
  2. Adnan

    June 24, 2015 at 5:46 am

    Thanks a lot dear.
    your below provided formula help me out.

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

    Reply
    • Mynda Treacy

      June 24, 2015 at 7:58 am

      Glad we could help, Adnan 🙂

      Reply
  3. Fay Camargo

    May 7, 2014 at 6:31 am

    This was extremely helpful…Thanks so much saved me a great deal of time.

    Reply
    • Mynda Treacy

      May 7, 2014 at 8:46 am

      You’re welcome, Fay 🙂

      Reply
  4. Rajesh

    July 17, 2013 at 1:20 pm

    It’s great !

    Thanks

    Reply
    • Mynda Treacy

      July 17, 2013 at 4:48 pm

      You’re welcome, Rajesh 🙂

      Reply
  5. Tom Black

    April 25, 2013 at 9:33 am

    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

      April 25, 2013 at 7:56 pm

      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

    October 14, 2012 at 3:26 pm

    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
    • Mynda Treacy

      October 14, 2012 at 8:44 pm

      Hi Kevin,

      The TRIM function should fix this. If it isn’t then I suspect there is some other problem with your data. I’d be happy to take a look if you want to send me the file.

      Kind regards,

      Mynda.

      Reply
  7. Prana Dey

    July 24, 2012 at 3:21 am

    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
    • Mynda Treacy

      July 24, 2012 at 1:25 pm

      Cheers, Prana 🙂 Glad to have helped.

      Reply
      • Prana Dey

        July 25, 2012 at 1:47 am

        Thanks and same to you. Have a great day.

        Reply
  8. Alwi

    April 28, 2012 at 12:10 am

    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
    • Mynda Treacy

      April 30, 2012 at 10:27 pm

      Hi Alwi,

      I’d use the Text to Columns tool. Use Delimited and colon as your delimiter. It’s much easier to use than trying to create a formula.

      HTH.

      Mynda.

      Reply
  9. richard

    April 14, 2012 at 11:39 pm

    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

      April 17, 2012 at 9:47 pm

      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

    January 22, 2012 at 9:24 am

    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
    • paul

      January 22, 2012 at 9:26 am

      P S The space between a href in the link has to stay, of course.

      Reply
    • Mynda Treacy

      January 22, 2012 at 9:30 pm

      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

  1. Excel CLEAN Formula • My Online Training Hub says:
    September 11, 2014 at 4:55 pm

    […] you might think that the TRIM function can replace blank spaces at the end of text, but unfortunately it can’t handle these special […]

    Reply
  2. Excel Formulas - My Online Training Hub says:
    September 9, 2014 at 10:34 pm

    […] TRIM, CHAR and SUBSTITUTE formula […]

    Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

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.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x