| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find position of first non-matching character | Excel Worksheet Functions | |||
| Generating a random 17-character alphanumeric string | Excel Discussion (Misc queries) | |||
| Sorting Alphanumeric data in Excel 2003 | Setting up and Configuration of Excel | |||
| sort on character position | Excel Worksheet Functions | |||
| locking data/cells so re-sorting a column doesn't re-position my d | Excel Discussion (Misc queries) | |||
Linear Mode
