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

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### mikerickson

##### MrExcel MVP
If you do a straight alphabetic sort you get issues like "10"<"2",
You could put leading 0s on everything with =LEFT("000000"&A1, 6) and sort that

#### Zelligar

##### New Member
If you do a straight alphabetic sort you get issues like "10"<"2",
You could put leading 0s on everything with =LEFT("000000"&A1, 6) and sort that

I'm not well versed in Excel so I think I'll need more clarification. I copied and pasted =LEFT("000000"&A1, 6) into a new column "B" the values were all zeros, six (6) of them. I inserted the formula into all the cells in column B. So I'm not doing something correctly.

#### mikerickson

##### MrExcel MVP
I'm sorry, that formula is wrong, it should be =RIGHT("0000000"&A1, 6)

#### Zelligar

##### New Member

When using =RIGHT("0000000"&A1, 6) the data with a 1a, 1b, 1c sorted under 19; 2a, 2b, 2c sorted under 20 etc. Is there a way for 1a, 1b. 1c to sort under 1 etc? Included is a thumbnail of the current result.

#### Attachments

• RCKY MTN VRS version 2.PNG
14.8 KB · Views: 4

#### mikerickson

##### MrExcel MVP
I forgot about the trailing characters
try =RIGHT("0000000"&A1, 6)&IF(ISNUMBER(A1)," ","")

#### Zelligar

##### New Member

Thank you for the formula, however it didn't sort in sequence. Another person I've been chatting with came up with a formula. column Q is =CONCATENATE(U1,T1) column S is =T(A1) column T is =IF(S1="","",RIGHT(S1,1)) column U is =IF(S1="",LEFT(A1,4),LEFT(S1,(LEN(S1)-1))) column V =_xlfn.NUMBERVALUE(U1) sorted by column V then T. I hope that makes since.

I appreciate your time and help on this inquiry. Have a good evening.

#### Peter_SSs

##### MrExcel MVP, Moderator
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),"")

#### Zelligar

##### New Member
I will give this a try as well and give them both to my partner and see how he likes the two. Thank you for the simpler version. Our data sets run from the hundreds to the 10's of thousands. Thank you for showing me this formula as an additional option. Have a good day!

#### Peter_SSs

##### MrExcel MVP, Moderator
Our data sets run ... to the 10's of thousands.
Hmm, I'll be interested to see how such a formula approach goes for you in terms of speed, especially when you do the sort.
If it is too slow, there may be other options to investigate.

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,296
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.

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