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?

Uncle Al wrote:
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?
----------------------

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

Al

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

Copy a blank cell and then editpaste 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
HIS

"Uncle Al" <Uncle wrote in message
...
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?

Nick Hodge wrote:

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

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

Just curious...

Bill

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 specialValues + Add or copy a 1, paste specialvalues +
multiply, both of which nudges Excel to 'behave'

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"Bill Martin -- (Remove NOSPAM from address)"
wrote in message ...
Nick Hodge wrote:

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

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

Just curious...

Bill

Nick Hodge wrote:
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 specialValues + Add or copy a 1, paste specialvalues +
multiply, both of which nudges Excel to 'behave'

-----------

Thanks Nick. Always nice to learn something new...

Bill

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

Bill Martin -- (Remove NOSPAM from address) wrote:

Nick Hodge wrote:

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


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

Just curious...

Bill