Sorting a List by letters rather than the first character.

  • Thread starter Thread starter NigelW
  • Start date Start date
N

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. :) :) :
 
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
 
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. :) :) :)
Click to expand...

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
 
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
Click to expand...

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
 
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
 
Back
Top