Sorting alpha-numerically

[Jim885]

Does anyone know how to use, or set-up the sort function so that I can sort a column of alpha-numeric numbers so I end up with this;

[TABLE="width: 48"]
<colgroup><col style="width: 48pt;" width="64"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]PL1[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL3[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL4[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL6[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL7[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL8[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL9[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL10[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL11[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL12[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL13[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL14[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL15[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL16[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL17[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL18[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL18A[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL19[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL20[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL21[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL22[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL23[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL24[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL25[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL26[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL27[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL29[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL29A[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL30[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL31[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL31A[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL33[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL34[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL35[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL36[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]PL37[/TD]
[/TR]
</tbody>[/TABLE]



and not this;

[TABLE="width: 48"]


<colgroup><col style="width: 48pt;" width="64">
<tbody>[TR]

[TD="class: xl65, width: 64, bgcolor: transparent"]PL1[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL10[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL11[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL12[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL13[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL14[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL15[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL16[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL17[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL18[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL18A[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL19[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL20[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL21[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL22[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL23[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL24[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL25[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL26[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL27[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL29[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL29A[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL3[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL30[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL31[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL31A[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL33[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL34[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL35[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL36[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL37[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL4[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL6[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL7[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL8[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]PL9[/TD]

[/TR]


</tbody>[/TABLE]

 

[Jim885]

Keep in mind that the list goes from PL1 to PL1500 with many alpha-numeric items being PL100A, PL215A, PL215B, PL215C and the like. I'm hoping to keep them in sequence with each other rather than jumping from what you see as the second result when using SORT.

Thanks.

 

[Jim885]

Keep in mind that the list goes from PL1 to PL1500 with many alpha-numeric items being PL100A, PL215A, PL215B, PL215C and the like. I'm hoping to keep them in sequence with each other rather than jumping from what you see as the second list when using SORT.

Thanks.

 

[Jim885]

I'm using Excel 2010, with Windows7. Is there a macro or any formula that could get the result I need?

 

[Gary's Student]

With a formula, perhaps in another column:

=--SUBSTITUTE(SUBSTITUTE(A1,"PL",""),"A","")

and then sort both columns by this "helper" column.

By The Way

How did you post that nicely formatted column??

 

[Teethless mama]

Assuming your data start in A1:Axxxx

In B1: =IF(ISNUMBER(RIGHT(A1,1)+0),TEXT(MID(A1,3,9),"0000.000"),TEXT(MID(A1,3,LEN(A1)-3)+CODE(RIGHT(A1))/1000,"0000.000"))

copy down to Bxxxx
select columnA & columnB, sort on columnB

 

[Jim885]

Gary,
That formula works nicely as long as there is only an "A" at the end of any number (PL118A). But when the number is followed by a B, C, D, N or M, the formula result is #VALUE and those items go to the end of the list.
[TABLE="width: 139"]
<tbody>[TR]
[TD]PL1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]PL3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]PL4
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]PL6
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]PL7
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]PL8
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]PL9
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]PL10
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]PL11
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]PL12
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]PL13
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]PL14
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]PL15
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]PL16
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]PL17
[/TD]
[TD]17
[/TD]
[/TR]
[TR]
[TD]PL18
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]PL18A
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]PL19
[/TD]
[TD]19
[/TD]
[/TR]
[TR]
[TD]PL20
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]PL21
[/TD]
[TD]21
[/TD]
[/TR]
[TR]
[TD]PL22
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]PL23
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD]PL24
[/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]PL25
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]PL26
[/TD]
[TD]26
[/TD]
[/TR]
[TR]
[TD]PL27
[/TD]
[TD]27
[/TD]
[/TR]
[TR]
[TD]PL29
[/TD]
[TD]29
[/TD]
[/TR]
[TR]
[TD]PL29A
[/TD]
[TD]29
[/TD]
[/TR]
[TR]
[TD]PL30
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]PL31
[/TD]
[TD]31
[/TD]
[/TR]
[TR]
[TD]PL31A
[/TD]
[TD]31
[/TD]
[/TR]
[TR]
[TD]PL33
[/TD]
[TD]33
[/TD]
[/TR]
[TR]
[TD]PL34
[/TD]
[TD]34
[/TD]
[/TR]
[TR]
[TD]PL35
[/TD]
[TD]35
[/TD]
[/TR]
[TR]
[TD]PL36
[/TD]
[TD]36
[/TD]
[/TR]
[TR]
[TD]PL37
[/TD]
[TD]37
[/TD]
[/TR]
[TR]
[TD]PL39
[/TD]
[TD]39
[/TD]
[/TR]
[TR]
[TD]PL40
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]PL41
[/TD]
[TD]41
[/TD]
[/TR]
[TR]
[TD]PL41A
[/TD]
[TD]41
[/TD]
[/TR]
[TR]
[TD]PL42
[/TD]
[TD]42
[/TD]
[/TR]
[TR]
[TD]PL43
[/TD]
[TD]43
[/TD]
[/TR]
[TR]
[TD]PL44
[/TD]
[TD]44
[/TD]
[/TR]
[TR]
[TD]PL46
[/TD]
[TD]46
[/TD]
[/TR]
[TR]
[TD]PL47
[/TD]
[TD]47
[/TD]
[/TR]
[TR]
[TD]PL48
[/TD]
[TD]48
[/TD]
[/TR]
[TR]
[TD]PL50
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]PL50A
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]PL51
[/TD]
[TD]51
[/TD]
[/TR]
[TR]
[TD]PL54
[/TD]
[TD]54
[/TD]
[/TR]
[TR]
[TD]PL56
[/TD]
[TD]56
[/TD]
[/TR]
[TR]
[TD]PL57
[/TD]
[TD]57
[/TD]
[/TR]
[TR]
[TD]PL57A
[/TD]
[TD]57
[/TD]
[/TR]
[TR]
[TD]PL59
[/TD]
[TD]59
[/TD]
[/TR]
[TR]
[TD]PL60
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]PL61
[/TD]
[TD]61
[/TD]
[/TR]
[TR]
[TD]PL62
[/TD]
[TD]62
[/TD]
[/TR]
[TR]
[TD]PL63
[/TD]
[TD]63
[/TD]
[/TR]
[TR]
[TD]PL64
[/TD]
[TD]64
[/TD]
[/TR]
[TR]
[TD]PL65
[/TD]
[TD]65
[/TD]
[/TR]
[TR]
[TD]PL66
[/TD]
[TD]66
[/TD]
[/TR]
[TR]
[TD]PL66A
[/TD]
[TD]66
[/TD]
[/TR]
[TR]
[TD]PL67
[/TD]
[TD]67
[/TD]
[/TR]
[TR]
[TD]PL67A
[/TD]
[TD]67
[/TD]
[/TR]
[TR]
[TD]PL69
[/TD]
[TD]69
[/TD]
[/TR]
[TR]
[TD]PL70
[/TD]
[TD]70
[/TD]
[/TR]
[TR]
[TD]PL71
[/TD]
[TD]71
[/TD]
[/TR]
[TR]
[TD]PL72
[/TD]
[TD]72
[/TD]
[/TR]
[TR]
[TD]PL73
[/TD]
[TD]73
[/TD]
[/TR]
[TR]
[TD]PL74
[/TD]
[TD]74
[/TD]
[/TR]
[TR]
[TD]PL74A
[/TD]
[TD]74
[/TD]
[/TR]
[TR]
[TD]PL76
[/TD]
[TD]76
[/TD]
[/TR]
[TR]
[TD]PL77
[/TD]
[TD]77
[/TD]
[/TR]
[TR]
[TD]PL78
[/TD]
[TD]78
[/TD]
[/TR]
[TR]
[TD]PL79
[/TD]
[TD]79
[/TD]
[/TR]
[TR]
[TD]PL80
[/TD]
[TD]80
[/TD]
[/TR]
[TR]
[TD]PL81
[/TD]
[TD]81
[/TD]
[/TR]
[TR]
[TD]PL82
[/TD]
[TD]82
[/TD]
[/TR]
[TR]
[TD]PL83
[/TD]
[TD]83
[/TD]
[/TR]
[TR]
[TD]PL84
[/TD]
[TD]84
[/TD]
[/TR]
[TR]
[TD]PL85
[/TD]
[TD]85
[/TD]
[/TR]
[TR]
[TD]PL86
[/TD]
[TD]86
[/TD]
[/TR]
[TR]
[TD]PL87
[/TD]
[TD]87
[/TD]
[/TR]
[TR]
[TD]PL88
[/TD]
[TD]88
[/TD]
[/TR]
[TR]
[TD]PL89
[/TD]
[TD]89
[/TD]
[/TR]
[TR]
[TD]PL89A
[/TD]
[TD]89
[/TD]
[/TR]
[TR]
[TD]PL90
[/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD]PL90A
[/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD]PL91
[/TD]
[TD]91
[/TD]
[/TR]
[TR]
[TD]PL92
[/TD]
[TD]92
[/TD]
[/TR]
[TR]
[TD]PL93
[/TD]
[TD]93
[/TD]
[/TR]
[TR]
[TD]PL95
[/TD]
[TD]95
[/TD]
[/TR]
[TR]
[TD]PL97
[/TD]
[TD]97
[/TD]
[/TR]
[TR]
[TD]PL99
[/TD]
[TD]99
[/TD]
[/TR]
[TR]
[TD]PL99A
[/TD]
[TD]99
[/TD]
[/TR]
[TR]
[TD]PL100
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]PL100A
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]PL101
[/TD]
[TD]101
[/TD]
[/TR]
[TR]
[TD]PL101A
[/TD]
[TD]101
[/TD]
[/TR]
[TR]
[TD]PL102
[/TD]
[TD]102
[/TD]
[/TR]
[TR]
[TD]PL103
[/TD]
[TD]103
[/TD]
[/TR]
[TR]
[TD]PL104
[/TD]
[TD]104
[/TD]
[/TR]
[TR]
[TD]PL105
[/TD]
[TD]105
[/TD]
[/TR]
[TR]
[TD]PL105A
[/TD]
[TD]105
[/TD]
[/TR]
[TR]
[TD]PL106
[/TD]
[TD]106
[/TD]
[/TR]
[TR]
[TD]PL106A
[/TD]
[TD]106
[/TD]
[/TR]
[TR]
[TD]PL107
[/TD]
[TD]107
[/TD]
[/TR]
[TR]
[TD]PL107A
[/TD]
[TD]107
[/TD]
[/TR]
[TR]
[TD]PL108
[/TD]
[TD]108
[/TD]
[/TR]
[TR]
[TD]PL108A
[/TD]
[TD]108
[/TD]
[/TR]
[TR]
[TD]PL109
[/TD]
[TD]109
[/TD]
[/TR]
[TR]
[TD]PL110
[/TD]
[TD]110
[/TD]
[/TR]
[TR]
[TD]PL111
[/TD]
[TD]111
[/TD]
[/TR]
[TR]
[TD]PL112
[/TD]
[TD]112
[/TD]
[/TR]
[TR]
[TD]PL113
[/TD]
[TD]113
[/TD]
[/TR]
[TR]
[TD]PL114
[/TD]
[TD]114
[/TD]
[/TR]
[TR]
[TD]PL115
[/TD]
[TD]115
[/TD]
[/TR]
[TR]
[TD]PL116
[/TD]
[TD]116
[/TD]
[/TR]
[TR]
[TD]PL117
[/TD]
[TD]117
[/TD]
[/TR]
[TR]
[TD]PL118
[/TD]
[TD]118
[/TD]
[/TR]
[TR]
[TD]PL118A
[/TD]
[TD]118
[/TD]
[/TR]
[TR]
[TD]PL119
[/TD]
[TD]119
[/TD]
[/TR]
[TR]
[TD]PL120
[/TD]
[TD]120
[/TD]
[/TR]
[TR]
[TD]PL122
[/TD]
[TD]122
[/TD]
[/TR]
[TR]
[TD]PL123
[/TD]
[TD]123
[/TD]
[/TR]
[TR]
[TD]PL124
[/TD]
[TD]124
[/TD]
[/TR]
[TR]
[TD]PL125
[/TD]
[TD]125
[/TD]
[/TR]
[TR]
[TD]PL126
[/TD]
[TD]126
[/TD]
[/TR]
[TR]
[TD]PL100B
[/TD]
[TD]#VALUE!
[/TD]
[/TR]
[TR]
[TD]PL106B
[/TD]
[TD]#VALUE!
[/TD]
[/TR]
[TR]
[TD]PL118B
[/TD]
[TD]#VALUE!
[/TD]
[/TR]
[TR]
[TD]PL118C
[/TD]
[TD]#VALUE!
[/TD]
[/TR]
[TR]
[TD]PL118N
[/TD]
[TD]#VALUE!
[/TD]
[/TR]
[TR]
[TD]PL66B
[/TD]
[TD]#VALUE!
[/TD]
[/TR]
[TR]
[TD]PL89B
[/TD]
[TD]#VALUE!
[/TD]
[/TR]
[TR]
[TD]PL90B
[/TD]
[TD]#VALUE!
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gary's Student]

I did not account for a trailing character being anything but "A"
We need some kind of logic that looks at the last character of the source and if it is not a number, converts it to its CODE() value and divides the result by 100.
So PL90A would become 90.65
PL90B would become 90.66
etc.

 

[Gary's Student]

How about:

=--SUBSTITUTE(SUBSTITUTE(A1,"PL",""),"A","")+IF(ISNUMBER(--RIGHT(A1,1)),0,CODE(RIGHT(A1,1))/100)

 

[Jim885]

Gary,
I tried your above formula, but it still only solves when the PL item either ends with a digit or the letter A. When the PL number is followed by a B, C, etc, the result is #VALUE. I even changed the formula to: =--SUBSTITUTE(SUBSTITUTE(A1,"PL",""),RIGHT(A1,1),"")+IF(ISNUMBER(--RIGHT(A1,1)),0,CODE(RIGHT(A1,1))/100)
In that case it worked for all situations except when the PL number ended with just a digit, ie PL100, PL101, PL102, etc. In that case, the return value is 1, 0, or 10
See below;
[TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]PL100
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PL100A
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100.65
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PL100B
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100.66
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PL101
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PL101A
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]101.65
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PL102
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PL103
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PL104
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]PL105
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[/TR]
</tbody>[/TABLE]