sorting by number/letter

[Zelligar]

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.

Thank you for your time.

 

[Zelligar]

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.

 

[Peter_SSs]

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]

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
	A	B
1	Data	Helper
2	7	|0000000007
3	58	|0000000058
4	6	|0000000006
5	58c	|0000000058c
6	1b	|0000000001b
7	58a	|0000000058a
8	1c	|0000000001c
9	58b	|0000000058b
Custom sort

Cell Formulas
Range	Formula
B2:B9	B2	=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
	A	B
1	Data	Helper
2	1b	|0000000001b
3	1c	|0000000001c
4	6	|0000000006
5	7	|0000000007
6	58	|0000000058
7	58a	|0000000058a
8	58b	|0000000058b
9	58c	|0000000058c
Custom sort

Cell Formulas
Range	Formula
B2:B9	B2	=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