# sorting question

#### rech340

##### New Member
I have something like this:

1
1DUP
1TRIP
2
2DUP
2TRIP
etc. etc.

I want it to sort like this:

1
2
1DUP
2DUP
1TRIP
2TRIP
etc. etc.

It needs to look past the 1st character, and sort on the 2nd.

Thanks for any help!

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
will your numeric entries only ever have one digit (never 11TIP etc)? or do you mean sort on the first alpha character in the string, wherever it might be?

I think it will be the 1st alpha character

Hi there
If you only ever have one number before the text, you might like to try this.
If your list is in column A, put this formula in B1 and scroll it down. Then sort by column B

=IF(LEN(A1)>1,MID(A1,2,50),A1)

regards
Derek

Hi that does work for a one digit number, but I will more likely need to have it look at the 1st alpha character in the cell

Thanks!

Use the following formula (from Aladin) to extract the text portion of the string, then sort on that:

=TRIM(SUBSTITUTE(A1,LEFT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))),""))

Thanks so much for your help on this. It did work to a degree, but not completely.

This is how it sorted it:

1DUP
2DUP
3DUP
1TRIP
2TRIP
3TRIP

As you can see, I had more than 1 "1ADD" and it should put the "1's" before the 2's and so on.

Ideally, I need a way to tell it which character in the cell that it needs to use for the sort.

Thanks again.

do a 2 key sort, first by the column with the trim() in, then by the source data.

Thanks so much,

It worked!

Replies
1
Views
217
Replies
8
Views
568
Replies
9
Views
250
Replies
2
Views
255
Replies
4
Views
252

1,219,687
Messages
6,149,718
Members
450,910
Latest member
DianeG

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