# 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.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### 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

Thanks for that

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

#### dragons

##### New Member

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
826

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,193
Messages
5,768,770
Members
425,492
Latest member
blueexcel123

### 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.

### Which adblocker are you using?

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

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