sort a column of numbers with 100510 and 10160 so they are in th.

U

Uncle Al

I am sorting a list of numbers containing 100510 and 10160 and 100510 comes
out before 10160 which is out of order. How do I get them to sort properly?
 
G

Guest

Uncle said:
I am sorting a list of numbers containing 100510 and 10160 and 100510 comes
out before 10160 which is out of order. How do I get them to sort properly?
Click to expand...
----------------------

Are you sure they are numbers and not text? To check if your "number"
(in A1 for example) is really text, over in an empty cell somewhere put:

[ ]=istext(A1)

That will return either "true" or "false" telling you if it's really text.

To convert the column to numbers, you can highlight all the cells in the
column and then click Format > Cells > Number. After that, they should
all sort correctly.

Good luck...

Bill
 
N

Nick Hodge

Al

As Bill has pointed out, they may be text. Sometimes simply reformatting may
not do it.

Copy a blank cell and then edit>paste special...>values+add the 'text'
numbers and they should budge to numbers. (Don't do this over formulae or
you will kill it/them)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
G

Guest

Nick said:
As Bill has pointed out, they may be text. Sometimes simply reformatting may
not do it.
Click to expand...

Will it legitimately not reformat to numbers sometimes or are you
talking about a known bug?

Just curious...

Bill
 
N

Nick Hodge

Bill

Sometimes Excel can be stubborn, it's a sort of 'bug'. It's more prevalent
with imported data from other apps and we tend to get tons of questions on
here with VLOOKUPs and stuff that doesn't appear to work.

It is not possible in these cases to just re-format, so we give either the
copy blank - paste special>Values + Add or copy a 1, paste special>values +
multiply, both of which nudges Excel to 'behave'

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
G

Guest

Nick said:
Bill

Sometimes Excel can be stubborn, it's a sort of 'bug'. It's more prevalent
with imported data from other apps and we tend to get tons of questions on
here with VLOOKUPs and stuff that doesn't appear to work.

It is not possible in these cases to just re-format, so we give either the
copy blank - paste special>Values + Add or copy a 1, paste special>values +
multiply, both of which nudges Excel to 'behave'
Click to expand...
 
J

Jerry W. Lewis

Reformatting changes the display, not the value of the cell contents. I
am NOT aware of any circumstance where reformatting WILL change text to
a number. By design, it prepares the cell to receive a number, if you
then re-enter the number (or select on the value in the formula bar and
press enter) then it will become a number, but not just by changing the
format.

Jerry
 
Top