sorting question

[rech340]

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!

 

[PaddyD]

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?


Paddy

 

[rech340]

I think it will be the 1st alpha character

 

[Derek]

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]

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!

 

[PaddyD]

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

Paddy

 

[rech340]

Paddy,

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

This is how it sorted it:

1ADD
2ADD
1ADD
3ADD
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.

 

[PaddyD]

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

Paddy

 

[rech340]

Thanks so much,

It worked!