Sorting a List by letters rather than the first character.

[NigelW]

I have a list of chemical compound names which I want to sort i
alphabetical order. Examples are: 1,2-dimethylbutane, dimethylbutane
2,3,4-trimethylpentane. These need to be sorted by the first letter
not the numbers or spaces or commas, ie 'd', 'd' and 't' in the exampl
given above rather than 1, 2 and 'd' which is what Excel does b
default. Thank you in advance for your suggestions. :

 

[NigelW]

wes

 

[Bernie Deitrick]

Nigel,

You can use a User-Defined-Function to strip out the numbers, commas, and
dashes, and then sorted on the value returned by the function. Copy the code
and paste into a regular code module in your workbook.

Used like
=Stripped(A1)

Copy the formula as needed to match your list, then sort based on the column
with the formula.

HTH,
Bernie
MS Excel MVP

Function Stripped(inCell As Variant)
Dim i As Integer

Stripped = inCell
For i = 0 To 9
Stripped = Replace(Stripped, CStr(i), "")
Next i
Stripped = Replace(Stripped, ",", "")
Stripped = Replace(Stripped, "-", "")
Stripped = Replace(Stripped, " ", "")
End Function

 

[Ron Rosenfeld]

I have a list of chemical compound names which I want to sort in
alphabetical order. Examples are: 1,2-dimethylbutane, dimethylbutane,
2,3,4-trimethylpentane. These need to be sorted by the first letter,
not the numbers or spaces or commas, ie 'd', 'd' and 't' in the example
given above rather than 1, 2 and 'd' which is what Excel does by
default. Thank you in advance for your suggestions.

You could set up a helper column and sort on that.

For example, if your data is in column A, and your helper column is column B
(but it does not have to be adjacent), then:

B1:= =MID(A1,MATCH(TRUE,CODE(MID(A1,ROW(INDIRECT("1:255")),1))>=65,0),255)

That will strip off the non-alpha stuff.

Then sort by Column B.

If the order of 1,2-dimethylbutane, dimethylbutane is important, you could
first sort by Column B, then Column A (in the same operation).


--ron

 

[Ron Rosenfeld]

You could set up a helper column and sort on that.

For example, if your data is in column A, and your helper column is column B
(but it does not have to be adjacent), then:

B1:= =MID(A1,MATCH(TRUE,CODE(MID(A1,ROW(INDIRECT("1:255")),1))>=65,0),255)

That will strip off the non-alpha stuff.

Then sort by Column B.

If the order of 1,2-dimethylbutane, dimethylbutane is important, you could
first sort by Column B, then Column A (in the same operation).


--ron

I forgot to mention that the above is an array formula.

To enter an array formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.
--ron

 

[TKT-Tang]

Please refer to the following thread :-

http://groups.google.com.sg/groups?...ang+max&hl=en&lr=&ie=UTF-8&c2coff=1&scoring=d

Regards.

 

[Dave Hawley]

Try this function to pull out the text names

=MID(A1,IF(ISERR(FIND("-",A1)+1),1,FIND("-",A1)+1),256)

Then copy paste special-values

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[NigelW]

Folks

Each of your suggestions have worked really well (and i am learning
something as well). Thank you all for your time and suggestions

Nigel