Untrimmable Spaces – Excel Formula

So how was your Christmas / New Year break? We had a quiet Christmas. Then we hiked a few mountains and camped in bush for few days. New Year was quiet too (as most of the family & friends are in India, while we are in NZ). As usual, we cooked and ate delicious food, had a few drinks and spent time playing cards, building stuff with Lego and drawing silly portraits of each other. I even squeezed in a bit of cycling.

Let’s talk about the untrimmable spaces.

We all know that TRIM() removes extra spaces from the beginning, ending and middle of a text.

So for example, if A1 has ” something and    one   more    ”

TRIM(A1)

will give “something and one more”

We can use CLEAN() function to remove non-printable characters (like the ASCII codes 0 to 31).  Of course, SPACE is technically a printable character, so CLEAN() won’t remove spaces.

The untrimmable spaces…?

The other day Sreekanth emailed me a sample of data and asked, “how do I remove the spaces in this list and convert them to numbers?”

Naturally I tried to TRIM().

But the data won’t budge. See below.

Hmm, let’s investigate why.

By using CODE(LEFT(B4,1)), we can check the code for the very first character in the value ”  4,124,500.00 ”

It is 160.

Hmm, 160, where have I met you before?

Poking in the Insert > Symbol tells us that 160 is the no break space. What an appropriate name that is.

In fact no break space is HTML character   ie spaces printed on a web page. (Just to be clear not every space on a web page is char 160, but sometimes you have   instead of SPACE.)

So how to remove the no break spaces?

Simple, we use SUBSTITUTE().

The formula =SUBSTITUTE(B4,CHAR(160),””) removes all the CHAR(160)s from B4.

But we want the number…

We can add one extra step to the above SUBSTITUTE() formula to convert text in B4 to number.

The below formula does the trick.

=SUBSTITUTE(SUBSTITUTE(B4,CHAR(160),””),”-“,””)+0

It replaces all CHAR(160)s and -s with blanks and then adds 0 to the resulting text thus forcing Excel to convert text to number. Bingo.

So there you go. For every byte of dirty data that sneaks in to Excel, there are eight ways to fight it out, at least.

Download Example Workbook

If all this space business sounds like rocket science, then check out the example workbook to understand the formulas better.

How do you remove untrimmable spaces?

If it is a one time job, I usually rely on Find Replace to get rid of any symbols, characters or phrases. But if I have lots of data or changing data, I use SUBSTITUTE, TRIM, CLEAN.

What about you? How do you remove untrimmable spaces? Please share your tips in the comments.