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.

Or watch on YouTube: Get Character Number With Excel CODE Function

______________________

You may also like...

2 Responses

  1. Joe Stephens says:

    Hello Debra –

    Good tips.
    I could not help but notice the TRIM function was not mentioned in this tutorial. Any particular reason why?

Leave a Reply

Your email address will not be published. Required fields are marked *