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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

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
 

Forum statistics

Threads
1,147,688
Messages
5,742,629
Members
423,745
Latest member
rtaylor1987

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