sorting by number/letter

Zelligar

New Member
I have not been able to figure out or find a solution to sorting the data that we have into an order that we can work with.

Some numbers have no other associated data and other numbers have an associated a,b or c. We want to sort the data so it reads 1, 1a,1b, 1c.... 6, 7, 8, ... 58, 58a, 58b, ... for example.

I've tried formulas found on various websites and none do the job. The two (2) thumbnails show the column we want to sort by. If you can help or have suggestions that would be great.

Zelligar

New Member
At the moment I'm doing small data sets from 100 to 200 points. I haven't had an opportunity to work with any data sets today, hoping for tomorrow morning.

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Peter_SSs

MrExcel MVP, Moderator
If anything more is required it would be good to know ..
• How big the number parts might actually be.
• Whether any alpha part is only ever one lower case letter. If not some more information about what variety can occur would help.

RAJESH1960

Active Member
Here is another option that I think does what you want with just a single helper column. You could reduce the number of leading zeros if your initial numbers are short. I have allowed for leading numbers up to 10 digits.

20 09 01.xlsm
AB
1DataHelper
27|0000000007
358|0000000058
46|0000000006
558c|0000000058c
61b|0000000001b
758a|0000000058a
81c|0000000001c
958b|0000000058b
Custom sort
Cell Formulas
RangeFormula
B2:B9B2=TEXT(LEFT(A2,AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1)),"|0000000000")&REPLACE(A2,1,AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1),"")

Now sort on 'Helper' column. The Helper column can be hidden if you want.
Result of sort:

20 09 01.xlsm
AB
1DataHelper
21b|0000000001b
31c|0000000001c
46|0000000006
57|0000000007
658|0000000058
758a|0000000058a
858b|0000000058b
958c|0000000058c
Custom sort
Cell Formulas
RangeFormula
B2:B9B2=TEXT(LEFT(A2,AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1)),"|0000000000")&REPLACE(A2,1,AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1),"")
x

Replies
0
Views
242
Replies
5
Views
135
Replies
0
Views
57
Replies
1
Views
88
Replies
21
Views
454

1,130,129
Messages
5,640,295
Members
417,135
Latest member
zeusmining

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.

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