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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
285
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

JGordon11

Active Member
Joined
Jan 18, 2021
Messages
285
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Sep 8, 2014
Messages
9

ADVERTISEMENT

Thanks for that
1629338720736.png


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

dragons

New Member
Joined
Sep 8, 2014
Messages
9

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
Joined
Jan 18, 2021
Messages
285
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 
Solution

dragons

New Member
Joined
Sep 8, 2014
Messages
9
thank you, that worked a treat.
i feel that all those wasted hours may have been worth it now.

Thanks again

1629340389344.png
 

Forum statistics

Threads
1,147,735
Messages
5,742,866
Members
423,760
Latest member
photogfrog

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