SUBSTITUTE for Space Removal – Another Way To TRIM!

The SUBSTITUTE function is an easy way to replace characters in an entry with something else.  Here is an example of a cell with data entered:

I want to replace “abc” with “def”.  In the cell next to it I enter the SUBSTITUTE function:

First I click on the cell I want to change followed by the old text, then the new in quotes:

And the substitution is complete:

Now let’s use SUBSTITUTE to remove spaces that are in the middle of an entry.  Start out the same way, but for the old text use a space in the quotes, and for the new text, use closed quotes:

And the space is removed:

This also works when you have multiple spaces in the entry:

And there you go!  BTW, this tip came from a follower of my Excel Bytes blog.  If you have a tip or suggestion, you can e-mail me through my blog or just comment on the blog post and I will see what I can do to accommodate your request!  Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

6 Responses to SUBSTITUTE for Space Removal – Another Way To TRIM!

  1. Oz du Soleil April 26, 2013 at 3:43 pm #

    Interesting!

    TRIM hasn’t been a reliable function or maybe I just don’t know how to work it. I like the SUBSTITUTE solution.

    Yesterday, someone had a problem getting a VLOOKUP to work. I noticed that there were trailing spaces on some of the entries. The VLOOKUP would error out on "JE3116X" even though "JE3116X " was present.

    The trailing spaces were inconsistent but fortunately we were dealing with codes that had no spaces internally. So, I took everything, did a Text-to-Columns to clip off the spaces, and that solved the problem. It was a bit of effort because the Text-to-Columns needs space to expand, but it worked.

  2. Michael Rempel April 26, 2013 at 3:49 pm #

    Oz,

    Did you see my post on VLOOKUP that incorporated TRIM into the process? http://anothersetofeyes.biz/excel-bytes/2013/4/10/vlookup-the-right-direction-to-go

  3. Bob Watson April 26, 2013 at 9:20 pm #

    Nice article. Stray spaces cause so many problems in spreadsheets yet, as you show, they are easy to deal with.

    Since we like this article, we’ve added it to our collection of useful and interesting spreadsheet-related articles from the web at http://www.i-nth.com/resources/connexion

    Regards,

    Bob

  4. Michael Rempel April 26, 2013 at 10:58 pm #

    Bob,

    Thanks, hope you find many of my posts informative!

  5. andrew wolfe April 27, 2013 at 11:07 pm #

    For eliminating multiple spaces or if I am substituting characters, I like to use Find and Replace (Ctrl+H).

    There are times when many of my customers try to use a complex function to do something that is already available in the Office Suite.

    I like to use =substitute() in conditional situations. But for a simple replacement, Find and Replace is quick and easy and accurate.

  6. Michael Rempel April 28, 2013 at 3:29 pm #

    Good point, Andrew. I did a quick test and found that the examples I used above could be accomplished with Find & Replace, finding a space and replacing it with nothing.

Leave a Reply