Sorting by Numeric then Alpha, with leading zeros

dragons

New Member
Joined
Sep 8, 2014
Messages
9
Hi, Have spent a bit of time on this, hope you can help.
Other answers on the web dont quite work for one reason or another.

i have alphanumeric numbers in a large dataset (dataset 1 is first)
3
5
10A
11B
12
15
99
123B
176
234
1056A

The 2nd dataset has leading zeros on them to aid with a data issue we had.
0003
0005
0010A
0011B
0012
0015
0099
0123B
0176
0234
1056A

Can I have some formulas thats sorts the 1st and 2nd dataset numerically, as shown in the preferred order above.

Can i also have formula's that can add and remove the leading zeros properly incl. the alphanumeric's.

im trying to join the two datasets back together and need a common join.

Thanking in Advance.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
What version of excel do you have? If not 365 then you need to enter the formula with s CTRL+SHIFT+ENTER not just ENTER
 
Upvote 0
Thanks for that
1629338720736.png


Thanks for that, looks like it has worked , so thankful.
stay safe
 
Upvote 0
Hi, just one more thing thats bothering me.
How to remove the leading zeros from the 2nd dataset, so i can reverse join back the other way.
 
Upvote 0
misc examples.xlsm
DE
100033
200055
30010A10A
40011B11B
5001212
6001515
7009999
80123B123B
90176176
100234234
111056A1056A
Sheet12
Cell Formulas
RangeFormula
E1:E11E1=MID(D1,MATCH(FALSE,MID(D1,ROW($1:$10),1)="0",0),99)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Solution
thank you, that worked a treat.
i feel that all those wasted hours may have been worth it now.

Thanks again

1629340389344.png
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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