sorting in excel

[mollymoo]

i want to sort some numbers in excel. however it will sort it as 1 then 10 11 12 13 14 15 16 17 18 19 2 20 21...

so the 2 gets put with the 20's and so on with the 3 with the 30's

but i need it to go 1 2 3 4 etc etc...

please help me sort in this order as it is driving me nuts having to cut and paste them into the correct order...

thanks

[weeal]

set the cell type for that column to general, it should sort numerically

[mollymoo]

it is set as general.. it is just that it will not sort as 1 2 3 4 .. the 2 get put with the 20's and so on....

[MrBen5]

Hmmm, you would have probably been better calling them 01 02 03 etc.

[weeal]

when mine is set to general it sorts them ok
and i did a wee trial sheet to check it
hmmm

[mollymoo]

everytime i put it in with a 0 and put in a 0 it takes it out...

[mollymoo]

weeal maybe i should point out they have to have a letter before them

for example.. ew1 ew23 ew3 etc etc....

[weeal]

just checked again up to mid 30's and it works ok
u using excell 2000?

[weeal]

och closed it again too lol

[mollymoo]

i am using excel 2007 and have to put a letter before it...

does it work for you with letetrs and then numbers???

[weeal]

either alpha or numeric it wont work mixed as it sees first number after the last letter
put letter in cell then number in next cell is all i can advise

[Whickerman]

If you dod want to add the zero, simply put an apostrophe before it - '01. Once you leave the cell the ' disappears.

But weeal's right - that's working on mine too...

[Huderon]

If you always have two letters in front of the number, create a new column in your worksheet and use the formula =VALUE(RIGHT(A1,LEN(A1)-2)) and fill the formula down to the end of your data. This will give you just the number part and you can then sort on the new column. That of course assumes your numbers are in column A - you will need to adjust the formula to use the correct column letter.

[Stephen_G]

Not sure if it'll still work, but I used to type in the numbers 1 - 4 in the first column (A1, A2 etc.), then highlight all four boxes and then place my cursor on the little box that would appear in the bottom right hand corner of A4, drag the cursor down column A and that would automatically put the numbers in numerical order.

Hope this (is easy to understand) helps.

[ACtheTROLL]

you are absolutely right
http://support.microsoft.com/kb/319827

however
the combination of letters and numbers should allow you to enter ac01 or 01ac without loosing the 0

if you just enter 01 ... it tidys the leading zero
but letters and numbers should be ok (no need for a ' ... but you can if you want)

if you had ac0.... and ad0... it sorts all the ac then all the ad numerically

[beso]

Am I missing something here? If you want numbers to sort in numerical order you need to format the column as Number.

General takes a guess at the formatting and sorts in alphanumeric order if it has any doubt.