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

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 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]HIS

"Uncle Al" <Uncle [email protected]> wrote in message
news:[email protected]...
>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 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]HIS

"Bill Martin -- (Remove NOSPAM from address)" <[email protected]>
wrote in message news:eMxfl%[email protected]...
> 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 special>Values + Add or copy a 1, paste special>values +
> 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