Excel Numbers Do Not Sort or Add Correctly
Last week, I heard from someone who was having a problem sorting some numbers in Excel. He sent me a small sample file that showed a few of the dates and numbers that just wouldn't sort correctly.
My first guess was that the data had been copied from a website – that can cause some strange behaviour, when you paste it into Excel. A quick check with the COUNT and COUNTA functions showed that none of the values in cells C3:C6 were real numbers – they were text.
Quick Fix Attempt #1
There's a page on my website, that shows a few solutions for fixing numbers that don't add up, or that don't sort correctly.
One quick fix, that works most of the time, is to copy a blank cell, then paste into the number cells, using the Add operation.
It works most of the time, but didn't work on these numbers.
Quick Fix Attempt #2
The next thing I tried was Convert Text to Columns. It's quick and easy, but didn't change these "text" numbers to real numbers.
When the quick fixes failed, I guessed that there were hidden characters in the cells, and the LEN function showed that my guess was correct. Each cell had 5 characters – not just the 3 numbers that were visible. You can see the LEN results in column D, in the screen shot below.
I copied the formula across, and found the culprits – there were two hidden characters at the end of each number, with the code 160 – a non-breaking space.
Find and Replace Non-Breaking Spaces
To quickly get rid of the non-breaking spaces, you can use the Find and Replace feature.
- Select the cells that contain the hidden characters
- On the Ribbon, click the Home tab
- Click the Find & Select button (at the far right)
- Click the Replace command
- For Find what, press the Alt key, and on the number keypad, press 0160 (nothing will appear in the box)
NOTE: If you're using a keyboard that doesn't have a separate number keypad, use the special keys to activate the "number keypad" section of the keyboard
- Leave the "Replace with" box empty
- Click Replace All
Fix With a Macro
If you run into this problem frequently, you can use a macro to replace all the hidden non-breaking spaces in a selected range of cells. There is sample code on my website, and you can copy that to your own workbook, and run it when necessary.
Go to this link to get the code: Fix Hidden Characters With VBA
Watch the Video
To see how the CODE function works, in a different example, you can watch this short video.
Or watch on YouTube: Get Character Number With Excel CODE Function