Hi,

I manage a magazine subscription list in Excel. The subscription number is
made up of 3 characters - the first being the first letter of the surname
and the second being the next unique number in the surname letter series, ex
Smith would be S1 and Sloan would be S2 and so forth. When these are sorted
subscription numberwise, Excel will sort S1, then S10 and then go on to S2,
S20 etc. Is there any way to make excel sort it as S1, S2, S3 and so on?

Thanks in advance,
Marzban

Only by changing your number allocation process to S01, S02 etc

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=555323

Hi Marzban,

You could use a helper column, assuming your S1, S10 etc. are in A1 down,
in B1 put =VALUE(MID(A1,2,3)) and copy down as far as your last
value then use sort ascending on column B.

The formula will work for your entire data but you will have to do a
seperate
sort for each letter of the alphabet. Now if someone can figure a way
that allows for the different letters we might have something useful.

HTH
Martin

Hi Marzban,

Addition to last post

In C1 put =LEFT(A1,1) and copy down
then do sort ascending on column B followed by sort ascending
on column C.

HTH
Martin



You could use a helper column, assuming your S1, S10 etc. are in A1 down,
in B1 put =VALUE(MID(A1,2,3)) and copy down as far as your last
value then use sort ascending on column B.

The formula will work for your entire data but you will have to do a
seperate
sort for each letter of the alphabet. Now if someone can figure a way
that allows for the different letters we might have something useful.

HTH
Martin

Hi Martin,

Now if someone can figure a way
that allows for the different letters we might have something useful

B1: =LEFT(A1,1)
C1: =--MID(A1,2,2)

Now Sort by Col B (asc) and Col C (asc)


---
Regards,
Norman



"MartinW" wrote in message
...
Hi Marzban,

You could use a helper column, assuming your S1, S10 etc. are in A1 down,
in B1 put =VALUE(MID(A1,2,3)) and copy down as far as your last
value then use sort ascending on column B.

The formula will work for your entire data but you will have to do a
seperate
sort for each letter of the alphabet. Now if someone can figure a way
that allows for the different letters we might have something useful.

HTH
Martin