Sort the digits in the cell value using a VBA function-CodePudding

Can any one help me with VBA code. I want to sort the number in ascending and descending order in excel but a single cell value. For instance, if user entered 5634 in "A1" cell i want the result in "A2" as "3456" (i.e ascending) and in "A3" "6543" (i.e. descending). is this possible in vba??

CodePudding user response：

Try this (put it in a VBA code module):

Public Function SortDigits(NumberIn As Variant, Optional SortOrder As Variant) As Variant
  Dim InVal As String, i As Integer, j As Integer, TempVal As String
  Dim bSortOrderAsc As Boolean
  
  SortDigits = CVErr(xlErrNull)
  If IsMissing(SortOder) Then
      bSortOrderAsc = True
  ElseIf SortOrder = "A" Then
      bSortOrderAsc = True
  ElseIf SortOrder = "D" Then
      bSortOrderAsc = False
  Else
      SortDigits = CVErr(xlErrValue)
      Exit Function
  End If
      
     
  If IsNumeric(NumberIn) Then
     InVal = CStr(NumberIn)
     For i = 1 To Len(InVal) - 1
         For j = i   1 To Len(InVal)
             If (bSortOrderAsc And Mid(InVal, i, 1) > Mid(InVal, j, 1)) Or _
                (Not bSortOrderAsc And Mid(InVal, i, 1) < Mid(InVal, j, 1)) Then
                'if Ascending order, this is wrong, swap them
                 TempVal = Mid(InVal, i, 1)
                 Mid(InVal, i, 1) = Mid(InVal, j, 1)
                 Mid(InVal, j, 1) = TempVal
            End If
        Next j
     Next i
     SortDigits = InVal
  Else
     SortDigits = CVErr(xlErrNum)
  End If
End Function

and call from an excel cell as follows:

=SortDigits(A1,"D")

Where A1 is the cell you want to sort the digits. Omit the second parameter or use "A" to sort in ascending order.

This is not necessarily very fast because it involves string manipulation (may not perform well if you have thousands of cells to calculate).