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)
Next

'--- 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

Worksheets("config").Activate

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

'--- Now Sort that Column with all values

srange = "A1:" & srange
Range(srange).Select
ActiveWorkbook.Worksheets("config").sort.SortFields.Clear
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
.Apply
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
Next
Worksheets("Sheet2").Activate
input_str = ""
For i = 1 To l
input_str = input_str & str_array(i)
Next

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

Range("A2").Value = input_str

'--- Delete that config file now

Application.DisplayAlerts = False
Worksheets("config").Delete
Application.DisplayAlerts = True

End Sub


cover3d_0-89071700_1484285537__1_

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