VBA Sort Alphanumeric comma delimited text in a cell

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
524
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
Platform
  1. Windows
Is it Possible to sort a cell with comma delimited alphanumeric text, I know it can be done using a helper column but I was wondering if it can be done by string/arrays,
Ideally I would like to pass the cell value to an array/string and sort that alphanumerically then pass the sorted text back to the same cell, Can someone help please

Example

From this
D11, D12, D13, D14, D2, D3, D4, D9 D10

To this
D2, D3, D4, D9, D10, D11, D12, D13, D14
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,377
Here's a custom formula, which uses VBA to sort a CSV string. Note that the first character of each value is ignored when sorting.

First, place the following code in a regular module (Visual Basic Editor >> Insert >> Module)...

VBA Code:
Option Explicit

Function SortCSVString(ByVal val As String, Optional delim As String = ",") As String

    Dim arr() As String
    arr() = Split(Replace(val, " ", ""), delim)
    
    SortArray arr
    
    SortCSVString = Replace(Join(arr, delim), delim, delim & " ")
    
End Function

Sub SortArray(ByRef arr() As String)

    Dim temp As Variant
    Dim i As Long
    Dim j As Long
    
    For i = LBound(arr) To UBound(arr) - 1
        For j = i + 1 To UBound(arr)
            If CLng(Mid(Trim(arr(i)), 2)) > CLng(Mid(Trim(arr(j)), 2)) Then
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
End Sub

Then you can use the following worksheet formula...

VBA Code:
=SortCSVString(A2, ",")

...where A2 contains your csv string. Note that since the delimiter is made optional, you can omit it from the formula...

VBA Code:
=SortCSVString(A2)

Hope this helps!
 

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
524
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
Platform
  1. Windows
Hi Domenic, I am getting runtime 6 overflow error on
VBA Code:
    If CLng(Mid(Trim(arr(i)), 2)) > CLng(Mid(Trim(arr(j)), 2)) Then
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,377
Can you post the actual string contained in the referenced cell?
 

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
524
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
Platform
  1. Windows
I used the example string posted above
 

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
524
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2010
  5. 2007
Platform
  1. Windows
Not sure why but it seems to be working now, Anyway thank you for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,113,942
Messages
5,545,114
Members
410,660
Latest member
marciabkin
Top