sorting question

rech340

New Member
Joined
Aug 27, 2002
Messages
7
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.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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
Joined
Aug 27, 2002
Messages
7

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

New Member
Joined
Aug 27, 2002
Messages
7

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
do a 2 key sort, first by the column with the trim() in, then by the source data.

Paddy
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top