Sorting Alphanumeric Data - Please Help!

[val_rucarean]

Hi guys.

I have this output in column A after the standard A to Z sort:

LFL388C10SUN

LFL388C11SUN

LFL388C12SUN

LFL388C13SUN

LFL388C14SUN

LFL388C1SUN

LFL388C2SUN

LFL388C3SUN

LFL388C4SUN

LFL388C5SUN PL22C10OPT PL22C11OPT PL22C12OPT PL22C1OPT PL22C2OPT PL22C3OPT <colgroup><col></colgroup><tbody> </tbody> <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

However the output I would like is:

LFL388C1SUN

LFL388C2SUN

LFL388C3SUN

LFL388C4SUN

LFL388C5SUN

<tbody>
</tbody>

LFL388C10SUN

LFL388C11SUN

LFL388C12SUN

LFL388C13SUN

LFL388C14SUN

PL22C1OPT PL22C2OPT PL22C3OPT PL22C10OPT PL22C11OPT PL22C12OPT <tbody> </tbody> <tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

The problem really is how to get Excel to sort the number after the "C" properly. I'm happy to get rid of "SUN" and "OPT" prior to sorting if that makes it easier.

Any help will be greatly appreciated.

Thanks
Val

 

[Special-K99]

Insert a Helper column
with this formula
=LEFT(A1,FIND("C",A1)-1)&MID(SUBSTITUTE(SUBSTITUTE(A1,"SUN",""),"OPT",""),FIND("C",A1)+1,LEN(A1)-FIND("C",A1)+1)
and sort on that column

UPDATE: Forget that, it wont work properly

 

[Special-K99]

Try this

Add 2 Helper columns
with data in column A

in column B
=LEFT(A1,FIND("C",A1)-1)
in column C
=MID(SUBSTITUTE(SUBSTITUTE(A1,"SUN",""),"OPT",""),FIND("C",A1)+1,LEN(A1)-FIND("C",A1)+1)

Sort on column B then on column C

 

[val_rucarean]

Try this

Add 2 Helper columns
with data in column A

in column B
=LEFT(A1,FIND("C",A1)-1)
in column C
=MID(SUBSTITUTE(SUBSTITUTE(A1,"SUN",""),"OPT",""),FIND("C",A1)+1,LEN(A1)-FIND("C",A1)+1)

Sort on column B then on column C

Hi Special-K99,

This works great for sorting the number after the "C" and I thank you for that but (silly enough) I failed to mention the first group of digits (column B) is equally important to sort correctly, any chance this can be fixed? You will see below that "LFL388" sits in between "LFL38" and "LFL71". Can your formula be improved to correct this?

A	B	C
LFL38C1SUN	LFL38	1
LFL38C2SUN	LFL38	2
LFL38C14SUN	LFL38	14
LFL388C1SUN	LFL388	1
LFL388C2SUN	LFL388	2
LFL388C3SUN	LFL388	3
LFL388C4SUN	LFL388	4
LFL388C5SUN	LFL388	5
LFL388C10SUN	LFL388	10
LFL388C11SUN	LFL388	11
LFL388C12SUN	LFL388	12
LFL388C13SUN	LFL388	13
LFL388C14SUN	LFL388	14
LFL71C2SUN	LFL71	2
PL22C1OPT	PL22	1
PL22C2OPT	PL22	2
PL22C3OPT	PL22	3
PL22C10OPT	PL22	10
PL22C11OPT	PL22	11
PL22C12OPT	PL22	12

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Many thanks
Val