Dear Readers,

By using Excel Macro, we can sort a String which is stored in a Cell in an Excel Sheet. Sorting will be done as a String. For example (Ascending): First all Numeric Values in ascending Order then Special Chars then Alphabets in ascending etc.

Sorting of String

Sorting of String

For this we need to copy and Paste below Sub Function in the Module of your Excel VBA. Now call this function from which ever sheet you want.

Sub Sorting()

Dim str_array(1000), temp, input_str As String
Dim flag As Boolean
flag = False

input_str = Range("A1").Value
l = Len(input_str)

'--- Store complete string in an array format letter by letter

For i = 1 To l
temp = Left(input_str, i)
str_array(i) = Right(temp, 1)

'--- We need to Sort all the letters in ascending or Descending Order
'--- For that i will add a Sheet in workbook and copy all the letters in one column and sort it by Excel Sort functionality

For Each Sheet In Sheets
If Sheet.Name = "config" Then
flag = True
End If
Next Sheet
If flag = False Then
Worksheets.Add().Name = "config"
End If

'--- Select that config Sheet


For i = 1 To l
srange = "A" & i
Range(srange).Value = str_array(i)

'--- Now Sort that Column with all values

srange = "A1:" & srange
ActiveWorkbook.Worksheets("config").sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("config").sort
.SetRange Range(srange)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With

'--- After sorting now Store back all the letters in the array variable

For i = 1 To l
srange = "A" & i
str_array(i) = Range(srange).Value
input_str = ""
For i = 1 To l
input_str = input_str & str_array(i)

'--- Put the sorted String in your cell where you want

Range("A2").Value = input_str

'--- Delete that config file now

Application.DisplayAlerts = False
Application.DisplayAlerts = True

End Sub


Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!