You have a column of numbers. Select the column. Totals appear in the status bar. But they are wrong. Turns out a lot of the numbers are not in the total. The green triangles are not showing up to indicate text. There is no apostrophe. What is going on? This episode includes an open letter to the Excel team from all of us, asking for TRIM and CLEAN to start handling CHAR(160) - the Non-Breaking Space.
Transcript of the video:
MrExcel podcast is sponsored Easy-XL.
All right, MrExcel podcast episode1898.
Numbers Are Not Adding Up in Status Bar?!
Hey, just a quick note.
So, it’s July 22nd, 2014, if you happen to be watching this today or tomorrow, 2 offers expiring soon.
I showed a FastExcel V3 a few weeks ago.
Charles Williams is offering 50%, you can save up to $94 through the end of the month at tinyurl.com/fastexcel.
Also, Mynda Treacy, our newest Excel MVP has an amazing Excel dashboard course that offered last year.
Enrollment is again open this year, but the 20% offer is only until July 24th.
I think like 8 PM Eastern.
So, jump in there tinyurl.com/exceldash.
All right, this is a bizarre, bizarre problem.
Someone wrote in and said, hey, I have a column of numbers and they are not totaling up down the status bar.
So, I selected the numbers here, right, and sure enough they’re totaling.
What’s the problem?
I don’t see a problem at all.
But then we start to look really small number 1.5, 2.9, hey, it definitely has to be more than 1.709.
So, you know I’m just kind of cruising around here, they add up, they add up, but right here somehow, that 2.993 we’re not getting additions anymore.
So, you know hey, this is simple, just select the data, Data, Text to Columns and Finish, and nothing changes.
That is my go-to trick, I do that all the time.
It converts the numbers stored as text to numbers, but there’s something wrong here.
So, let’s do equal this number + 0 and that one works, but again, that 2.993, 2.993 stops working.
What the heck is up with this?
So, then I said, all right, let’s do =TRIM and get rid of the leading and trailing spaces +0.
All right, and again it works with the numbers no problem and stops working there.
What is going on?
So, here’s what I did.
I said we’re going to do =LEN(, of that number and sure enough for some reason even though it’s 2.993, I’m getting 11.
All right, so let’s figure out what is happening.
Put in the number 1, hold down the control key while I drag the fill handle to get over to 11 and =MID(, of that number, press F4 to lock it down, comma, that position, comma 1.
Let’s break this out and so, we have something invisible there and then eventually, we start getting the 2.993.
So there’s 1, 2, 3, 4 cells or something invisible and then 1, 2 cells of something else invisible.
Let’s see what the invisible thing is with =CODE(, of that invisible thing.
There we are.
It is a character 160, which is they’re all 160's except for a 32 at the end.
Okay, so character 160.
Traditionally, character 32 is a regular space and Trim gets rid of regular spaces.
Data, Text to Column, finished gets rid of regular spaces if they’re at the end or the beginning.
But this 160 is a non-breaking space character, not breaking space and I can almost predict that this number had to come from the web because on the web in html with code up for a non-breaking space in order to force spaces to show up.
Usually in html, if you type space, it becomes just space.
But if you need three non-breaking spaces to show up, you would type that code three times and that evidently is what’s happened.
Now, it’s really hard to get rid of non-breaking space because you can’t type character 160 in to the Find and Replace box.
At least I don’t know how to.
I’m sure someone will leave a note in the YouTube comments and tells me what the secret key stroke is to do that, but my method is to go to =CHAR(160), which puts that invisible non-breaking space there.
We just have to trust that it’s there on the home tab and we’ll do COPY and then PASTE, PASTE as values.
All right so, now, I have that non-breaking space there.
I’m going to go to EDIT mode and sure enough, there is a character there that we can’t see.
I’m going to use Shift+Left Arrow to select that invisible character.
I’m going to control+C to put that on the clip board, come back here to our numbers that have problems, control+H for Find and Replace, Find what, I’m going to do control+V. See, I actually paste the invisible thing in there and Replace with nothing.
Do Replace All.
All done. We made 28 replacements.
And let’s see all right, but that actually solved our problem.
So there, I selected the numbers and 11.094 we are good to go.
What a bizarre, bizarre problem because they look like numbers, there’s no little green warning tag there that, hey, this is a number, stored as text.
That character 160 which you know, is a modern day plague because of people using it in html.
Excel certainly does not treat that.
Well, the CLEAN function.
If any of you are out there shouting “CLEAN, use CLEAN,” CLEAN does not get rid of 160.
It’s about time that we update the CLEAN function to handle character 160 and maybe even the Trim function to handle character 160 because it’s so prevalent.
I’m really glad this person noticed that it was adding up.
You know, there’s a good chance we had a few of those scattered in with other numbers that were right.
No one would have been able to work with a horrible, horrible problem.
All right, well hey, I want to thank you for stopping by.
We’ll see you next time for another netcast with MrExcel.
All right, MrExcel podcast episode1898.
Numbers Are Not Adding Up in Status Bar?!
Hey, just a quick note.
So, it’s July 22nd, 2014, if you happen to be watching this today or tomorrow, 2 offers expiring soon.
I showed a FastExcel V3 a few weeks ago.
Charles Williams is offering 50%, you can save up to $94 through the end of the month at tinyurl.com/fastexcel.
Also, Mynda Treacy, our newest Excel MVP has an amazing Excel dashboard course that offered last year.
Enrollment is again open this year, but the 20% offer is only until July 24th.
I think like 8 PM Eastern.
So, jump in there tinyurl.com/exceldash.
All right, this is a bizarre, bizarre problem.
Someone wrote in and said, hey, I have a column of numbers and they are not totaling up down the status bar.
So, I selected the numbers here, right, and sure enough they’re totaling.
What’s the problem?
I don’t see a problem at all.
But then we start to look really small number 1.5, 2.9, hey, it definitely has to be more than 1.709.
So, you know I’m just kind of cruising around here, they add up, they add up, but right here somehow, that 2.993 we’re not getting additions anymore.
So, you know hey, this is simple, just select the data, Data, Text to Columns and Finish, and nothing changes.
That is my go-to trick, I do that all the time.
It converts the numbers stored as text to numbers, but there’s something wrong here.
So, let’s do equal this number + 0 and that one works, but again, that 2.993, 2.993 stops working.
What the heck is up with this?
So, then I said, all right, let’s do =TRIM and get rid of the leading and trailing spaces +0.
All right, and again it works with the numbers no problem and stops working there.
What is going on?
So, here’s what I did.
I said we’re going to do =LEN(, of that number and sure enough for some reason even though it’s 2.993, I’m getting 11.
All right, so let’s figure out what is happening.
Put in the number 1, hold down the control key while I drag the fill handle to get over to 11 and =MID(, of that number, press F4 to lock it down, comma, that position, comma 1.
Let’s break this out and so, we have something invisible there and then eventually, we start getting the 2.993.
So there’s 1, 2, 3, 4 cells or something invisible and then 1, 2 cells of something else invisible.
Let’s see what the invisible thing is with =CODE(, of that invisible thing.
There we are.
It is a character 160, which is they’re all 160's except for a 32 at the end.
Okay, so character 160.
Traditionally, character 32 is a regular space and Trim gets rid of regular spaces.
Data, Text to Column, finished gets rid of regular spaces if they’re at the end or the beginning.
But this 160 is a non-breaking space character, not breaking space and I can almost predict that this number had to come from the web because on the web in html with code up for a non-breaking space in order to force spaces to show up.
Usually in html, if you type space, it becomes just space.
But if you need three non-breaking spaces to show up, you would type that code three times and that evidently is what’s happened.
Now, it’s really hard to get rid of non-breaking space because you can’t type character 160 in to the Find and Replace box.
At least I don’t know how to.
I’m sure someone will leave a note in the YouTube comments and tells me what the secret key stroke is to do that, but my method is to go to =CHAR(160), which puts that invisible non-breaking space there.
We just have to trust that it’s there on the home tab and we’ll do COPY and then PASTE, PASTE as values.
All right so, now, I have that non-breaking space there.
I’m going to go to EDIT mode and sure enough, there is a character there that we can’t see.
I’m going to use Shift+Left Arrow to select that invisible character.
I’m going to control+C to put that on the clip board, come back here to our numbers that have problems, control+H for Find and Replace, Find what, I’m going to do control+V. See, I actually paste the invisible thing in there and Replace with nothing.
Do Replace All.
All done. We made 28 replacements.
And let’s see all right, but that actually solved our problem.
So there, I selected the numbers and 11.094 we are good to go.
What a bizarre, bizarre problem because they look like numbers, there’s no little green warning tag there that, hey, this is a number, stored as text.
That character 160 which you know, is a modern day plague because of people using it in html.
Excel certainly does not treat that.
Well, the CLEAN function.
If any of you are out there shouting “CLEAN, use CLEAN,” CLEAN does not get rid of 160.
It’s about time that we update the CLEAN function to handle character 160 and maybe even the Trim function to handle character 160 because it’s so prevalent.
I’m really glad this person noticed that it was adding up.
You know, there’s a good chance we had a few of those scattered in with other numbers that were right.
No one would have been able to work with a horrible, horrible problem.
All right, well hey, I want to thank you for stopping by.
We’ll see you next time for another netcast with MrExcel.
