sort in numberical order

[Guest]

help! I can't seem to get excell to sort in real numerical order ie 1, 2, 3,
4, 5 and NOT 1, 10, 100, 2, 20, etc. my data is definatatly sorted in
number format
Using excell 2003
hope someone can help
thanks
Dawn

 

[Pete_UK]

I think your numbers are really text values that look like numbers, for
it to sort in the way you say.

Pete

 

[Guest]

How do i make sure there really numbers? all numbers are preceeded by the
letters TMB, could this be why?
Dawn

 

[Pete_UK]

Hi Dawn,

So, you have values like TMB1, TMB2, TMB10 etc in your column? If you
want these to be sorted correctly you should make the numerical part
have the same number of digits by inserting leading zeros.

Assume that these values are in column A starting in cell A2, and that
you could have up to 4 digits after the TMB part. In the first blank
column (eg F2), enter this formula:

="TMB"&TEXT(RIGHT(A2,LEN(A2)-3),"0000")

The 4 zeros at the end determine how many leading zeros to add - you
can increase this if necessary, then copy the formula down for as many
entries as you have in column A.

Now you should set your sort range to include this extra column, and
use that column as the sort key. If you wish, you could fix the values
in this column and then copy them into column A to overwrite the values
that are there - then you could delete the helper column.

Hope this helps.

Pete