# 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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

##### MrExcel MVP
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?

#### rech340

##### New Member
I think it will be the 1st alpha character

#### Derek

##### Well-known Member
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

#### rech340

##### New Member

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!

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

#### rech340

##### New Member

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.

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

#### rech340

##### New Member
Thanks so much,

It worked!

Replies
3
Views
162
Replies
2
Views
338
Replies
12
Views
209
Replies
4
Views
131
Replies
14
Views
108

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,578
Messages
5,765,205
Members
425,267
Latest member
bishopc22

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