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.

excel showing values to be sorted within a cell

Step 1

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

Open developer tab to write VBA to sort words in a cell

Step 2

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

Insert a module in the VB editor

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


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

        tmpHi = tmpHi – 1


     If (tmpLow <= tmpHi) Then

        tmpSwap = vArray(tmpLow)

        vArray(tmpLow) = vArray(tmpHi)

        vArray(tmpHi) = tmpSwap

        tmpLow = tmpLow + 1

        tmpHi = tmpHi – 1

     End If


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

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

End Sub

VBA code to sort values inside a cell inn excel

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.

result shows values are sorted within a cell value

Hope this helped.

Share The Knowledge

Random Posts