Sort series alphaNumerically

[sainathd]

Hi, I have few rows of data

[TABLE="width: 500"]
<tbody>[TR]
[TD]COL A[/TD]
[TD]COL B[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]C100[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]C101[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]C201[/TD]
[TD]0.7[/TD]
[/TR]
[TR]
[TD]C202[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]R3[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]R301[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]R302[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Expected Result
[TABLE="width: 500"]
<tbody>[TR]
[TD]COL A[/TD]
[TD]COL B[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]0.9[/TD]
[/TR]
[TR]
[TD]C100[/TD]
[TD]0.3[/TD]
[/TR]
[TR]
[TD]C101[/TD]
[TD]0.4[/TD]
[/TR]
[TR]
[TD]C200[/TD]
[TD]0.6[/TD]
[/TR]
[TR]
[TD]C201[/TD]
[TD]0.7[/TD]
[/TR]
[TR]
[TD]C202[/TD]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]R3[/TD]
[TD]0.9[/TD]
[/TR]
</tbody>[/TABLE]

Bottom Line I need to sort it as C1,C2,C3,.....C100,c101.. alphabet's first then numbers 1,2,3, sorting alpha numerically

Using build in SORT function I get C1,C100,C101.... I have like 500 rows as such, is there any way I could accomplish this with macro's?

 

[Swapnil Shah]

Dear Sainathd,

You can make use of helper formula , below formula will convert C1 to C001 , C2 to C002 depending on what is the max length available in a selected range. (in this case its 4 so it converts strings with less than 4 length to exactly 4 length by appending 0s before first number of string). Later you can sort helper column to achieve the desired result.

This is not the direct method but it will keep your data sanity intact. formula is assuming you have a data set in cells B2 to C10.

=LEFT($B2,SMALL(ISNUMBER(MID($B2,ROW(OFFSET($A$1,,,LEN($B2))),1)+0)*ROW(OFFSET($A$1,,,LEN($B2))),2)-1)&REPT("0",MAX(LEN($B$2:$B$10))-LEN($B2))&RIGHT($B2,LEN($B2)-SMALL(ISNUMBER(MID($B2,ROW(OFFSET($A$1,,,LEN($B2))),1)+0)*ROW(OFFSET($A$1,,,LEN($B2))),2)+1)

Also this is an array formula so press Ctrl+Shift+Enter and not enter only.

 

[SpillerBD]

If the assumption that the first character is always a Alpha character and there are no more than 3 digits, then you can get away with a simpler formula.

=LEFT(A2,1)&TEXT(VALUE(RIGHT(A2,LEN(A2)-1)),"000")

 

[sainathd]

It was helpful, thanks.

 

[Rick Rothstein]

If the assumption that the first character is always a Alpha character and there are no more than 3 digits, then you can get away with a simpler formula.

=LEFT(A2,1)&TEXT(VALUE(RIGHT(A2,LEN(A2)-1)),"000")

Based on your assumptions, this simpler formula would also work...

=LEFT(A2)&TEXT(MID(A2,2,3),"000")