Sorting alphanumeric data at 2nd, 3rd or 4th character position

K

kykles

I am trying to sort the following data (for example):
PL00/1235
PL04/1111
PL05/3211
PL88/0463
PL97/8546
PL99/2134
PROF02/5555
PROF05/6743
PROF06/7952
PROF88/1623
PROF97/1236
PROF99/1644
MED89/5263
MED97/2563
MED01/1256
MED05/1333

I would like to be able to sort by firstly the numbers following the letters
(which indicates the year) and secondly alphabetically by the letters in
front of those years (ie PL,PROF,MED) and thirdly by the number following the
forward stroke (/).

Does anyone know how this might be done?
 
J

Jarek Kujawa

quick and dirty:

in A1
=MID(A1,FIND("/",A1)-2,2)

in C1
=MID(A1,FIND("/",A1)+1,LEN(A1)-FIND("/",A1))

in D1
=MID(A1,1,FIND("/",A1)-3)

copy/drag down
then do the sorting

pls click YES if this helped
 
J

Jacob Skaria

Dear

You can use temporary columns with formulas to acheive this. Assuming you
have your data in ColA

In cell B1
=MID(A1,FIND("/",A1)-2,2)

In cell C1
=SUBSTITUTE(A1,B1&"/"&D1,"")

In Cell D1
=MID(A1,FIND("/",A1)+1,LEN(A1))

Copy the formulas down as needed

Select the Range A1:D##
From Menu>Data>Sort
By ColB,ColC,ColD

Once done you can remove the temporary columns B,C,D
 
K

kykles

Thanks.... very helpful & it did work.....I am still having trouble with the
"year" bit though (ie the 2 numbers after the letters) - it is sorting
00,01,02,03,04,05,06,07,08,09,88,89,90,91,92,93 etc - i need for the 80's to
be first, then 90's then 00's etc - any further help would be appreciated....
 
Top