Sorting by Numeric then Alpha, with leading zeros

dragons

New Member
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

JGordon11

Active Member
misc examples.xlsm
AB
130003
250005
310A0010A
411B0011B
5120012
6150015
7990099
8123B0123B
91760176
102340234
111056A1056A
Sheet12
Cell Formulas
RangeFormula
B1:B11B1=REPT("0",5-IFNA(MATCH(FALSE,ISNUMBER(--LEFT(A1,ROW(\$1:\$10))),0),LEN(A1)+1))&A1
Press CTRL+SHIFT+ENTER to enter array formulas.

dragons

New Member
misc examples.xlsm
AB
130003
25[ORMULA='{=REPT("0",5-IFNA(MATCH(FALSE,ISNUMBER(--LEFT(A2,ROW(\$1:\$10))),0),LEN(A2)+1))&A2}']0005[/FORMULA]
310A0010A
411B0011B
5120012
6150015
7990099
8123B0123B
91760176
102340234
111056A1056A
Sheet12
Cell Formulas
RangeFormula
B1:B11B1=REPT("0",5-IFNA(MATCH(FALSE,ISNUMBER(--LEFT(A1,ROW(\$1:\$10))),0),LEN(A1)+1))&A1
Press CTRL+SHIFT+ENTER to enter array formulas.

JGordon11

Active Member
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

dragons

New Member

ADVERTISEMENT

Thanks for that

Thanks for that, looks like it has worked , so thankful.
stay safe

dragons

New Member

ADVERTISEMENT

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.

JGordon11

Active Member
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.

dragons

New Member
thank you, that worked a treat.
i feel that all those wasted hours may have been worth it now.

Thanks again

Replies
1
Views
810

Threads
1,147,731
Messages
5,742,852
Members
423,758
Latest member
accountfornada

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

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