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

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?

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

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

On 29 Maj, 08:34, kykles wrote:
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?

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

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 MenuDataSort
By ColB,ColC,ColD

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

--
If this post helps click Yes
---------------
Jacob Skaria


"kykles" wrote:

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?

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

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....

"Jacob Skaria" wrote:

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 MenuDataSort
By ColB,ColC,ColD

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

--
If this post helps click Yes
---------------
Jacob Skaria


"kykles" wrote:

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?