# 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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### 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
137
Replies
0
Views
32
Replies
1
Views
65
Replies
21
Views
409
Replies
1
Views
77

1,126,947
Messages
5,621,773
Members
415,856
Latest member
jimb2k

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