Sort Comma Separated Values Within A Cell In Excel

In this post we will see how to sort values within a cell separated by commas.

We will use a quick sort algorithm to sort values within a cell, we will write VBA program to implement quick sort that will take care of sorting values within a cell in excel

Below is the data before sorting in excel cells in cell A1 that we want to sort and the new sorted result ll be stored in the same cell.

Step 1

Open VB editor from Developer tab in excel as shown in the figure.

Step 2

Insert a module in which we will write a VBA code to copy sort comma delimited values within a cell in excel.

Step 3

Now double click the newly created module to open that and paste the below code in the module.

Option Explicit

Public Sub SortVals()

Dim i As Integer

Dim arr As Variant

arr = Split(ActiveCell.text, “,”)

‘ trim values so sort will work properly

For i = LBound(arr) To UBound(arr)

arr(i) = Trim(arr(i))

Next i

‘ sort

QuickSort arr, LBound(arr), UBound(arr)

‘ load sorted values back to cell

Dim comma As String

comma = “”

ActiveCell = “”

For i = LBound(arr) To UBound(arr)

ActiveCell = ActiveCell & comma & CStr(arr(i))

comma = “,”

Next i

End Sub

Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)

Dim pivot As Variant

Dim tmpSwap As Variant

Dim tmpLow As Long

Dim tmpHi As Long

tmpLow = inLow

tmpHi = inHi

pivot = vArray((inLow + inHi) \ 2)

While (tmpLow <= tmpHi)

While (vArray(tmpLow) < pivot And tmpLow < inHi)

tmpLow = tmpLow + 1

Wend

While (pivot < vArray(tmpHi) And tmpHi > inLow)

tmpHi = tmpHi – 1

Wend

If (tmpLow <= tmpHi) Then

tmpSwap = vArray(tmpLow)

vArray(tmpLow) = vArray(tmpHi)

vArray(tmpHi) = tmpSwap

tmpLow = tmpLow + 1

tmpHi = tmpHi – 1

End If

Wend

If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi

If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi

End Sub

This code has two procedures that works together to separate comma separate values and sort them within the cell, the code splits the string, sort the values and create a new string out of the sorted values.

Now run the code by pressing F5 or by pressing play button in the VB editor.

We can see that code as sorted the comma separated values within the cell and replaced the unsorted value with the sorted string.