Jobs

Technology

sorting in excel

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

mollymoo Thurs 27/03/08 20:33

<< Previous

Next >>

Subscribe to question

Post an Answer

weeal
Thurs 27/03/08
20:38

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

mollymoo
Thurs 27/03/08
20:56

Question Author

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
Thurs 27/03/08
20:59

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

weeal
Thurs 27/03/08
21:00

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

mollymoo
Thurs 27/03/08
21:01

Question Author

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

mollymoo
Thurs 27/03/08
21:02

Question Author

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

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

weeal
Thurs 27/03/08
21:03

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

weeal
Thurs 27/03/08
21:04

och closed it again too lol

mollymoo
Thurs 27/03/08
21:05

Question Author

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

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

weeal
Thurs 27/03/08
21:06

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
Thurs 27/03/08
22:29

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
Thurs 27/03/08
23:00

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.

Judge Nutmeg
Thurs 27/03/08
23:09

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
Thurs 27/03/08
23:52

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
Fri 28/03/08
10:32

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.

Post an Answer

Submit the above question and answers

add to del.icio.us	add to digg	add to furl
add to reddit	add to Technorati	add to Blinklist
add to StumbleUpon	add to squidoo	add to ma.gnolia
add to Cocomment	add to Netscape	add to Fark