Sorting alpha-numerically

J

Jim885

Well-known Member
Joined
Jul 8, 2012
Messages
663
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]
 
Sort by date Sort by votes
Teethless mama said:
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
Click to expand...

Teethless mama,
Thanks. your formula works perfectly, and for every situation. Both you and Gary are brilliant at this stuff. The depth of these formulas are amazing. Even though I understand what these formulas are doing when I see them, I could have never put it all together to get the result. Thanks so much!! Amazing! Simply, amazing!
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
I think I got it. We need to test the last character of the data first:

=IF(ISNUMBER(--RIGHT(A1,1)),--RIGHT(A1,LEN(A1)-2),--MID(A1,3,LEN(A1)-3)+CODE(RIGHT(A1,1))/100)

Give this a try.
 
Upvote 0
Gary's Student,
Thanks for continuing to look for another answer. Your formula works perfectly too. Thank you, thank you, thank you!
You and Teethless mama are brilliant people. I appreciate the follow-up and consideration that was given to me by both of you. And thanks for not giving up on your formula.
Here's the result for a short string of item numbers that shows your formula working with trailing letters of A, B, C & N.

All I did to post this was to copy and paste a range of cells from the excel sheet.

[TABLE="width: 96"]


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

[TD="class: xl63, width: 64, bgcolor: transparent"]PL100[/TD]

[TD="class: xl64, width: 64, bgcolor: transparent"]100[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL100A[/TD]

[TD="class: xl64, bgcolor: transparent"]100.65[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL100B[/TD]

[TD="class: xl64, bgcolor: transparent"]100.66[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL101[/TD]

[TD="class: xl64, bgcolor: transparent"]101[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL101A[/TD]

[TD="class: xl64, bgcolor: transparent"]101.65[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL102[/TD]

[TD="class: xl64, bgcolor: transparent"]102[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL103[/TD]

[TD="class: xl64, bgcolor: transparent"]103[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL104[/TD]

[TD="class: xl64, bgcolor: transparent"]104[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL105[/TD]

[TD="class: xl64, bgcolor: transparent"]105[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL105A[/TD]

[TD="class: xl64, bgcolor: transparent"]105.65[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL106[/TD]

[TD="class: xl64, bgcolor: transparent"]106[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL106A[/TD]

[TD="class: xl64, bgcolor: transparent"]106.65[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL106B[/TD]

[TD="class: xl64, bgcolor: transparent"]106.66[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL107[/TD]

[TD="class: xl64, bgcolor: transparent"]107[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL107A[/TD]

[TD="class: xl64, bgcolor: transparent"]107.65[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL108[/TD]

[TD="class: xl64, bgcolor: transparent"]108[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL108A[/TD]

[TD="class: xl64, bgcolor: transparent"]108.65[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL109[/TD]

[TD="class: xl64, bgcolor: transparent"]109[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL110[/TD]

[TD="class: xl64, bgcolor: transparent"]110[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL111[/TD]

[TD="class: xl64, bgcolor: transparent"]111[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL112[/TD]

[TD="class: xl64, bgcolor: transparent"]112[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL113[/TD]

[TD="class: xl64, bgcolor: transparent"]113[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL114[/TD]

[TD="class: xl64, bgcolor: transparent"]114[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL115[/TD]

[TD="class: xl64, bgcolor: transparent"]115[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL116[/TD]

[TD="class: xl64, bgcolor: transparent"]116[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL117[/TD]

[TD="class: xl64, bgcolor: transparent"]117[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL118[/TD]

[TD="class: xl64, bgcolor: transparent"]118[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL118A[/TD]

[TD="class: xl64, bgcolor: transparent"]118.65[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL118B[/TD]

[TD="class: xl64, bgcolor: transparent"]118.66[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL118C[/TD]

[TD="class: xl64, bgcolor: transparent"]118.67[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL118N[/TD]

[TD="class: xl64, bgcolor: transparent"]118.78[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL119[/TD]

[TD="class: xl64, bgcolor: transparent"]119[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL120[/TD]

[TD="class: xl64, bgcolor: transparent"]120[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL122[/TD]

[TD="class: xl64, bgcolor: transparent"]122[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL123[/TD]

[TD="class: xl64, bgcolor: transparent"]123[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL124[/TD]

[TD="class: xl64, bgcolor: transparent"]124[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL125[/TD]

[TD="class: xl64, bgcolor: transparent"]125[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent"]PL126[/TD]

[TD="class: xl64, bgcolor: transparent"]126[/TD]

[/TR]


</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

Sunline
  • Question Question
Macro Alter
Replies
0
Views
358
Sunline
Sunline

Forum statistics

Threads
1,224,586
Messages
6,179,716
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top