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.



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".