VBA to Sort Data Alphanumerically

mfast

New Member
Joined
May 29, 2015
Messages
19
I have many different lines of part numbers that have similar data to the example below that I would want a VBA to sort alphanumerically. I just cant figure it out. Help will be much appreciated.

Excel Sort:

32583
274842
274843
274844
274845
274846
274847
641288A
641316A
641317A
641318A
641319A
641320A
641321A
641322A
641333A
641345A
641346A
641347A
641348A
641359A
641660A
641663A
641664A
641665A
641666A
641667A
641668A
641669A
641671A
641672A
641712A
64315A
64469A
64562A
CP1403
CP1404
CP1405
CP818
CP819
CP830
CP831

What I am looking for:

CP818
CP819
CP830
CP831
CP1403
CP1404
CP1405
32583
274842
274843
274844
274845
274846
274847
64315A
64469A
64562A
641288A
641316A
641317A
641318A
641319A
641320A
641321A
641322A
641333A
641345A
641346A
641347A
641348A
641359A
641660A
641663A
641664A
641665A
641666A
641667A
641668A
641669A
641671A
641672A
641712A
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
The data type is Text, but I bet some of the data is recorded as numbers (General). You need to have consistency and make everything Text. Use Text to Columns to convert everything to Text, then resort. Use "Sort numbers and numbers stored as text separately"
:oops:
Oh wait, your on a different but similar problem... hows that go again....
 

mfast

New Member
Joined
May 29, 2015
Messages
19
I import all the data as Text as not to lose anything that may start with a zero.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
Is your desired sort strictly on the numeric portion, or are you trying to reverse the relation of numerals to alpha characters in the ASCII sort?
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710

ADVERTISEMENT

Here's what seems crude. Use a helper column to sort on. Use this formula to update the value being sorted....
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"0","^"),"1","{"),"2","|"),"3","}"),"4","~"),"5","'"),"6",","),"7",CHAR(132)),"8",CHAR(139)),"9",CHAR(140))
 

mfast

New Member
Joined
May 29, 2015
Messages
19
Here's what seems crude. Use a helper column to sort on. Use this formula to update the value being sorted....
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"0","^"),"1","{"),"2","|"),"3","}"),"4","~"),"5","'"),"6",","),"7",CHAR(132)),"8",CHAR(139)),"9",CHAR(140))


Ok, I see where you are going with that...but that is not working. I added that to column B and sorted it A to Z and now its in a really wacky order.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,710
... Your sample sorts right when set from "Z-A". But I bet your real data is still a fail even with that...:oops:
So I went and double-checked what the built in sort order of the character map is....:oops: it sorts the "z"'s at the end, so a "Substitute" strategy woul have to be extended to the entire character set !

Are you sure you don't want to reconsider....
 

mfast

New Member
Joined
May 29, 2015
Messages
19
... Your sample sorts right when set from "Z-A". But I bet your real data is still a fail even with that...:oops:
So I went and double-checked what the built in sort order of the character map is....:oops: it sorts the "z"'s at the end, so a "Substitute" strategy woul have to be extended to the entire character set !

Are you sure you don't want to reconsider....

I really appreciate your help. Yes you are correct, the real data is a fail. It is not able to sort the alpha characters correctly.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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
Top