Custom Sort Question

It looks like you want to create a list that contains everything to the right of a dash ("-") if a dash occurs, or simply everything if no dash is present.

Here is a formula that worked when I tested it for this case. It is a slight variation from a formula solution Aladin posted a couple months ago for a similar problem, so many thanks to Aladin.

Assuming your list starts in cell A2, enter this in another column (B2 or wherever):

=IF(ISNUMBER(SEARCH("-",A2)),RIGHT(A2,LEN(A2)-SEARCH("-",A2)),A2)

Now you should be able to sort off this new list without those prefixes getting in the way.

Any help?

Tom Urtis

Thanks, Tom, it partially works but I should have explained in the original post that some chemicals might have more than just a single number prefix (eg 1,2-cis-xxxx). Getting a bit complicated now but is there a way of looking for the first letter of a chemical AND ignoring certain prefixes such as cis- and trans- etc? I was hoping there would be a way to do this automatically without having another column but I would imagine this would be VB only, which would be taking a sledgehammer to this nut.

: I have a list of chemicals which are stored in various places at work which I'd like to sort alphabetically. The problem is many of the chemicals start with a number which I want to be ignored by the sort routine, eg 1-nitropropane should come under 'n' etc. To further complicate matters some chemicals are isomers and their names can start with cis-, trans-, (R)- or (S)- etc. I'm a bit unclear on how to create a custom list which could be used for this and don't particularly want to start using VBA (it tends to put people here off when they open something up and it tells them it has macros!). Any help would be gratefully appreciated.

Nothing could be easier than using a custom sort...

...just...

1. List your chemicals on a worksheet
2. Select the list
3. Choose the Tools | Options... Custom Lists
menu command, press [ Import ] and [ OK ]

...after choosing the Data | Sort... menu command
you can invoke this custom sort order by...

1. Press [ Options ]
2. Choose the chemical list from the drop down
list
3. Press [ OK ] twice.

Re: Nothing could be easier than using a custom sort...

Er, yeah, in general maybe but it just sorts them alphanumerically (?) ie 1-nitropropane would come near the start, not under 'n'. 2. Select the list 3. Choose the Tools | Options... Custom Lists menu command, press [ Import ] and [ OK ] you can invoke this custom sort order by... 2. Choose the chemical list from the drop down list 3. Press [ OK ] twice. : I have a list of chemicals which are stored in various places at work which I'd like to sort alphabetically. The problem is many of the chemicals start with a number which I want to be ignored by the sort routine, eg 1-nitropropane should come under 'n' etc. To further complicate matters some chemicals are isomers and their names can start with cis-, trans-, (R)- or (S)- etc. I'm a bit unclear on how to create a custom list which could be used for this and don't particularly want to start using VBA (it tends to put people here off when they open something up and it tells them it has macros!). Any help would be gratefully appreciated.

No so! It sorts them in the order they were imported...

If your list is...

{"Cu"
;"Al"
;"He"
;"N"
;"Li"
;"Na"}

...they will be sorted in that order. Review my
instructions (below) and/or read the Excel Help
topics for...

1. Create a custom fill series or sorting order
2. Change or delete a custom fill series or sorting order
3. Sort months, weekdays, or custom lists

Er, yeah, in general maybe but it just sorts them alphanumerically (?) ie 1-nitropropane would come near the start, not under 'n'. : ...just... : 1. List your chemicals on a worksheet : 2. Select the list : 3. Choose the Tools | Options... Custom Lists : menu command, press [ Import ] and [ OK ] : ...after choosing the Data | Sort... menu command : you can invoke this custom sort order by... : 1. Press [ Options ] : 2. Choose the chemical list from the drop down : list : 3. Press [ OK ] twice. :

Here's an unelegant workaround

Hi Mudface,

There is probably a formula out there that can do this in one fell swoop, but what I did to work around this was first, enter the formula in cell B2 and copy down, which took care of the first instances of prefixes. Then I copied the formula over to C2 to copy down and it got rid of the second instances where they existed. This is an unelegant approach but it worked. I'm sure if someone later today will arrive at a formula that will make my suggestion look like an old Victrola record player, but it's just a suggestion to maybe get you through for the short term.

Tom U.

Take 2, try this formula

Mudface,

See if this works better than my first suggestion:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))-0,RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,"-","*",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))))

Tom Urtis

Suppose...

...your list (in column B) looks like this...

{"nitropropane"
;"1-nitropropane"
;"oxygen"
;"cis-nitropropane"
;"(R)-nitropropane"
;"glycerin"
;"nitroglycerin"}

Enter the following formula into cell C1 and
Copy down...

=IF(ISNUMBER(FIND("-",B1)),RIGHT(B1,LEN(B1)-(FIND("-",B1)))&","&LEFT(B1,FIND("-",B1)),B1)

...and sort on column C. I have a list of chemicals which are stored in various places at work which I'd like to sort alphabetically. The problem is many of the chemicals start with a number which I want to be ignored by the sort routine, eg 1-nitropropane should come under 'n' etc. To further complicate matters some chemicals are isomers and their names can start with cis-, trans-, (R)- or (S)- etc. I'm a bit unclear on how to create a custom list which could be used for this and don't particularly want to start using VBA (it tends to put people here off when they open something up and it tells them it has macros!). Any help would be gratefully appreciated.

Re: Take 2, try this formula

A more aggressive formula is possible, if we could have an exhaustive list of prefixes.

Aladin Mudface, See if this works better than my first suggestion: =IF(LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))-0,RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,"-","*",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))))