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.

numbertext01

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.

numbertext02

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.

numbertext04

Digging Deeper

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.

Next, I used the CODE function, with the MID function, to show each character in the string. Here is the formula in cell E3:

=IFERROR(CODE(MID($C3,E$2,1)),””)

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.

numbertext06

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

numbertext05

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.

And for more Excel advanced sorting tips, go to the Excel Add-in Advanced Sorting page on my Contextures site.

__________________

2 thoughts on “Excel Numbers Do Not Sort or Add Correctly”

  1. I had a similar problem with dates not converting. The dates had non breaking spaces embedded between the elements. I found / recognized the problem by copying the dates into Word. Using the Show/Hide feature uncovered the non-breaking spaces. Replacing them to normal spaces and copying back to Excel fixed the problem for me.

  2. I am having a problem sorting in excel when I put a time in (i.e. 8:00am) and then go to sort the sheet it is not sorting dates, then time properly. How do I fix this? I tried chaging sort A to Z and Z to A it doesnt fix the issue!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.