Search Not Just Numbers

Tuesday 5 November 2013

Excel Tip: Removing all spaces from text

Before we get into today's tip, I'm aware that we have a lot of new visitors and subscribers and I thought it might be a good time to remind you of my needaspreadsheet.com service.

If you are reading this blog, then you probably use Excel in your job, and my posts are intended to help you to get the most out of this excellent tool. However, sometimes you might not have the time or the skills to get it to do what you want.

When that happens, you just need to enter what you need your spreadsheet to do in the form on the site, and I will send you a fixed price quote to build (or fix) your spreadsheet for you. The whole service is handled by email and as a result, your location really doesn't matter - we have regular clients all over the globe.

Anyway, enough about me - on with today's tip...

In an earlier post on tidying up text, I introduced the TRIM function, that removes all leading and trailing spaces from text, as well as ensuring that any spaces in the text are reduced to single spaces.

Whereas this is useful for many applications to tidy up data, sometimes we need to remove all spaces from text. This happened to me the other day with some client data from their bank account. The only way to ensure consistency of the data was to remove all spaces.

Thankfully, Excel has a function that can help with this, as well as a number of other issues we might face when manipulating text data.

The function we can use is SUBSTITUTE. This replaces all references to one string of text in a cell with another string of text.

Its syntax is:

=SUBSTITUTE(Text to apply the formula to,Old text,New text,[Instance number])

Text to apply the formula to - this can be text in inverted commas, but is usually a cell reference containing the text

Old text - the text that you wish to see replaced, this can be text (in inverted commas) or a reference to text

New text - the text that you wish to see Old text replaced with, this can be text (in inverted commas) or a reference to text

Instance number - this argument is optional (as indicated by the square brackets) and specifies which instance of Old text you wish to replace (1 for the first instance, 2 for the second, etc.). If this argument is not entered, the function replaces all instances of Old text.

We can use this function to remove all spaces from the text in cell A1, by entering the following in cell B1:

=SUBSTITUTE(A1," ","")

Note that the second argument is a space in inverted commas, whereas the third argument has no space.

B1 will now be the same as A1, but we will have replaced all instances of a space with nothing - i.e. removing all of the spaces.

It's as simple as that.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

8 comments:

  1. Alternatively, Ctrl+H (find and replace function).

    Select the cells you need changed, press Ctrl+H, under find type a space, replace leave blank, press OK and viola!

    ReplyDelete
    Replies
    1. Yes Tim, that works great if it is a one-off exercise. My approach is designed more for where this cleansing is an ongoing requirement.

      Delete
    2. Replace is the quickest option for me as you can apply it live rather than needing to create new rows or columns.

      Delete
  2. Thank you. Just what I needed!

    BR

    Jens

    ReplyDelete
  3. It doesnt work ..i have tried both and i am tierd.

    ReplyDelete
    Replies
    1. I'm sure if you post the copies of the formula you used and the yext you applied it to (both copied from your spreadsheet). I will be able to spot the problem.

      Delete
  4. Didn't work for me either :(

    ReplyDelete
  5. It didn't work for me either. Bummer :(

    ReplyDelete