[Solved] Sort by 1st uppercase in the cell

[mukhis]

Here is one interesting thing (I guess) from chemistry. I would like to sort by 1st uppercase in the cell. For example, 5 rows in a column have:
2-Butene
Butane
n-Hexane
Hexane
N-Methyl-2-pyrrolidone

(In chemistry, the uppercase is decided by the IUPAC nomenclature)

Normally, A-Z sort in Excel will give me the following:
2-Butene
Butane
Hexane
n-Hexane
N-Methyl-2-pyrrolidone

However, I would like to see:
Butane
2-Butene
Hexane
n-Hexane
N-Methyl-2-pyrrolidone

Thanks!

 

[Abrar-ur-Rahman Niloy]

Hello Mukhis.

Interesting take here. There is no default feature in Excel that can perform this kind of task as Excel prioritizes numbers over alphabets while sorting. However, you can use VBA to create such custom order priorities. Here is a code that can sort the way you wanted in the example (with uppercase sort):

Sub SortbyFirstCapital()

Set rng = Selection
Set Rng2 = Range("D2") 'Insert your output cell here

Dim Arr() As Variant
ReDim Arr(1 To rng.Cells.Count)

Dim OriginArr() As Variant
ReDim OriginArr(1 To rng.Cells.Count)

Dim Arr2() As Variant
ReDim Arr2(1 To rng.Cells.Count)

For j = 1 To rng.Cells.Count
Value = rng.Cells(j).Value
For i = 1 To Len(Value)
If Asc(Mid(Value, i, 1)) >= 65 And Asc(Mid(Value, i, 1)) <= 90 Then
Arr(j) = Right(Value, Len(Value) - i + 1)
Exit For
End If
Next i
OriginArr(j) = Value
Next n

For n = LBound(Arr) To UBound(Arr)
Out = FindMax(Arr)
Arr2(UBound(Arr2) - n + 1) = OriginArr(Out(2))
Arr(Out(2)) = ""
Next n

For k = LBound(Arr2) To UBound(Arr2)
Rng2.Cells(n, 1) = Arr2(k)
Next n


End Sub


Function FindMax(Arr)

maxValue = Arr(LBound(Arr))
maxIndex = LBound(Arr)

For i = LBound(Arr) + 1 To UBound(Arr)
If StrComp(Arr(i), maxValue) = 1 Then
maxValue = Arr(i)
maxIndex = i
End If
Next i

Dim Out(1 To 2) As Variant
Out(1) = maxValue
Out(2) = maxIndex

FindMax = Out

End Function

Keep in mind, this code works based on the first upper case ignoring the numbers and small letters before hyphens (the ns). In the case of sorting between something like n,n-dimethyl formaldehyde and N-Hexane, It will put N-Hexane beforehand.

 

[mukhis]

Hello Mukhis.

Interesting take here. There is no default feature in Excel that can perform this kind of task as Excel prioritizes numbers over alphabets while sorting. However, you can use VBA to create such custom order priorities. Here is a code that can sort the way you wanted in the example (with uppercase sort):

Sub SortbyFirstCapital()

Set rng = Selection
Set Rng2 = Range("D2") 'Insert your output cell here

Dim Arr() As Variant
ReDim Arr(1 To rng.Cells.Count)

Dim OriginArr() As Variant
ReDim OriginArr(1 To rng.Cells.Count)

Dim Arr2() As Variant
ReDim Arr2(1 To rng.Cells.Count)

For j = 1 To rng.Cells.Count
Value = rng.Cells(j).Value
For i = 1 To Len(Value)
If Asc(Mid(Value, i, 1)) >= 65 And Asc(Mid(Value, i, 1)) <= 90 Then
Arr(j) = Right(Value, Len(Value) - i + 1)
Exit For
End If
Next i
OriginArr(j) = Value
Next n

For n = LBound(Arr) To UBound(Arr)
Out = FindMax(Arr)
Arr2(UBound(Arr2) - n + 1) = OriginArr(Out(2))
Arr(Out(2)) = ""
Next n

For k = LBound(Arr2) To UBound(Arr2)
Rng2.Cells(n, 1) = Arr2(k)
Next n


End Sub


Function FindMax(Arr)

maxValue = Arr(LBound(Arr))
maxIndex = LBound(Arr)

For i = LBound(Arr) + 1 To UBound(Arr)
If StrComp(Arr(i), maxValue) = 1 Then
maxValue = Arr(i)
maxIndex = i
End If
Next i

Dim Out(1 To 2) As Variant
Out(1) = maxValue
Out(2) = maxIndex

FindMax = Out

End Function

Keep in mind, this code works based on the first upper case ignoring the numbers and small letters before hyphens (the ns). In the case of sorting between something like n,n-dimethyl formaldehyde and N-Hexane, It will put N-Hexane beforehand.

Thanks for your kind reply and the VB code! Yes, I understand it has limitations. Honestly, the thing I am trying to achieve is fairly complex. So, let's see.