Removing blank characters, AKA Non-breaking spaces, from Excel text strings

If you have an issue where Excel’s TRIM function does not appear to be removing the blank spaces in your text string, or you are trying to remove blank characters/non-breaking spaces from a text string then read on to learn how to resolve the problem…



The majority of Excel analysts involved in data manipulation, especially when using external data sources, have come across the age-old problem of blank spaces at the front, or the end, of a text string.

Being a blank space they can be difficult to spot at the beginning of a text string and they are often impossible to spot when they appear at the end unless you click on the actual cell and check in more detail.

The common tactic to resolve a text string with blank spaces is to use Excel’s TRIM function, this will remove the leading or trailing spaces ensuring a text string starts and ends with a character, but occasionally you will come across this problem…blank characters or in web terms the non-breaking space!

One of my earlier articles will help any of you understand how to implement the TRIM function but an important point to note here is that the TRIM function will not remove blank characters; they need some specialist attention as I will now explain.

Blank characters! Surely they are the same as a blank space?

To put simply, no they are not.  To the naked eye they look exactly the same and it often takes using the TRIM function to find out that you are dealing with a blank character and not a blank space:

00052_Remove Blank Characters_01

As you can see from the above table a blank space at the front or the end of a string will be removed using Excel TRIM function, although when it is a blank character it may look the same to the eye but the text string remains unchanged.  As it is difficult to see these blanks I like to use Excels LEN function to return the length of the text string so I can see exactly what is happening.

What is the blank character or non-breaking space?

Without getting too technical the basics are that a computer operates using numbers, you may see letters and symbols but the computer thinks of it all as numbers, these numbers are called ASCII codes.

An ASCII code is a way that a computer can translate a character into a number so that it can understand and work with it.  So for example the ASCII code for the capital letter A is 65, for the capital letter Z it is 90.

You can view the ASCII code for a letter in using Excel’s CODE function (notice there is a difference between capital and lower case):

00052_Remove Blank Characters_02

The crucial bit

So here is why a blank space is different to a blank character:

  • A blank space is returned as an ASCII code of 32
  • A blank character, or non-breaking space, is returned as an ASCII code of 160, a very important difference.

00052_Remove Blank Characters_03

The above cells A2 and A3 may look empty but as the CODE formula result in column C shows cell A2 contains a blank space and cell A3 contains a blank character.

Why doesn’t the TRIM function work on both?

The TRIM function is set to look for and remove leading or trailing spaces which are a code 32.  That means whenever it comes across a code 160 (blank character/non-breaking space) it will just ignore it and leave it in the string.

How to remove blank characters/non-breaking spaces

My preferred way to tackle this issue is by combing two functions in Excel, the SUBSTITUTE function and the CHAR function.

The SUBSTITUTE function takes the following format:

=SUBSTITUTE(text, old text, new text)

This is where text refers to your current text string, old text refers to the text that you want to substitute and new text refers to the text you want to replace the old text with.

The CHAR function takes the following format:

=CHAR(number)

This where number refers to the ASCII code number of the character or symbol.

So from the earlier investigation we know that the ASCII code for a blank character, or non-breaking space, is 160 therefore we can use this to refer to the old text, we can then substitute it for a true blank which Excel recognises with the double quotation marks, “”.

The formula will look like:

=SUBSTITUTE(cell ref of text string, CHAR(160),””)

In practice:

00052_Remove Blank Characters_04

You can now see that in both examples we have reduced the length by 1 when using this new formula so even if you can’t see it by eye the blank character has now been removed.

Summary

We looked at how blank spaces in a text string are different to a blank character, which is also known as a non-breaking space.

We identified that in ‘computer speak’ a blank space takes the ASCII code 32, whereas the blank character has an ASCII code of 160.  The TRIM function will only remove ASCII codes of 32.

We used the SUBSTITUTE function in combination with the CHAR function to remove any blank characters from the text string.

Finally

This sort of problem is becoming more common in Excel because of the amount of web data that gets captured these days and subsequently analysed in Excel.

The non-breaking space is something most website designers and programmers will be very aware of, usually in the format   as it is used a great deal for alignment purposes in data tables and the like on websites, but for an Excel analyst we often only notice this issue when something like a VLOOKUP fails.

Now you know how to solve the problem when you next come across it, simple when you know how!

Keep Excelling,