How to arrange alphabet and numbers in a cell

[geniusideas]

Hi,

In one cell I have numbers and alphabets and how to arrange ascending
or descending for example
before
Cell A1 = 6532ADC
After
Cell A1 = ACD2356

what formula I have to use?
VBA code is preferable.
Thanks

 

[Bernie Deitrick]

Try the code below, used like

=SORTCELL(A1, TRUE)

HTH,
Bernie
MS Excel MVP

Function SortCell(myR As Range, OrdAsc As Boolean) As String
Dim myArr() As String
Dim myTemp As Variant
Dim i As Integer
Dim j As Integer

'Split the string into characters
ReDim myArr(1 To Len(myR.Value))
For i = 1 To Len(myR.Value)
myArr(i) = Mid(myR.Value, i, 1)
Next i

'Do the sort
For i = LBound(myArr) To UBound(myArr) - 1
For j = i + 1 To UBound(myArr)
If Ascending Then
If myArr(i) > myArr(j) Then
myTemp = myArr(j)
myArr(j) = myArr(i)
myArr(i) = myTemp
End If
Else
If myArr(i) < myArr(j) Then
myTemp = myArr(j)
myArr(j) = myArr(i)
myArr(i) = myTemp
End If
End If
Next j
Next i

'Return the sorted string

SortCell = Join(myArr, "")
End Function

 

[Bernie Deitrick]

And if you want to use it in code rather than as a worksheet function, something like

Dim myC As Range

For Each myC In Range("A1:A10")
myC.Value = SortCell(myC.Value, TRUE)
Next myC

HTH,
Bernie
MS Excel MVP

 

[geniusideas]

And if you want to use it in code rather than as a worksheet function, something like

Dim myC As Range

For Each myC In Range("A1:A10")
myC.Value = SortCell(myC.Value, TRUE)
Next myC

HTH,
Bernie
MS Excel MVP

Thank YOU very much...