Sorting a subscription list

  • Thread starter Thread starter Marzban J. Hathiram
  • Start date Start date
M

Marzban J. Hathiram

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
 
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
 
Hi Martin,
Now if someone can figure a way
that allows for the different letters we might have something useful
Click to expand...

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

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