Hill notation/sort (chemistry) in Excel

[MrShorty]

In chemistry, Hill notation is a common and systematic way of writing and sorting molecular formulas (http://en.wikipedia.org/wiki/Hill_system). Does anyone know of a sorting algorithm or published "custom list" for Excel to sort chemicals by formula using the Hill system? I'm sure I could build my own custom list (which, if complete, would be very long) or VBA algorithm, but I'm hoping that someone else has already done it.

[MrShorty]

Just in case it isn't clear, I'm after an algorithm for sorting a list of chemical formulas after they are written in Hill notation. My lists will have the formulas entered in Hill notation. What I'm looking for is a way to sort that list according to the conventions used in the Hill system.

[shg]

You could use a UDF to generate a sortable string, then sort on that:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

E.g., =HillPad(A1)

Some ambitious wag could do this in shorter form with a Regular Expression.

[MrShorty]

Thanks for your help.

I haven't yet had a chance to play with it. If I understand what you are trying to do, this function:
1) Takes the existing formula as input
2) Parses the formula
3) Creates a new text string for the formula that will then follow the normal alphabetic sorting rules

Does that sound right?

[shg]

Yup .

[MrShorty]

Seems to work pretty well. Thanks for your help.