VBA Sort Alphanumeric comma delimited text in a cell

decadence

Well-known Member
Joined
Oct 9, 2015
Messages
525
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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!
 
Upvote 0
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
 
Upvote 0
Can you post the actual string contained in the referenced cell?
 
Upvote 0
Not sure why but it seems to be working now, Anyway thank you for your help
 
Upvote 0
Is it possible to adapt the code in order to sort multiple letter substring?
eg DZ1, DZ100, DZ102, DZ22 > output should be DZ1, DZ22, DZ100, DZ102

DZ can be a multiple letter substring (OSC, JMP, SMTH etc)
 
Upvote 0
Is it possible to adapt the code in order to sort multiple letter substring?
eg DZ1, DZ100, DZ102, DZ22 > output should be DZ1, DZ22, DZ100, DZ102

DZ can be a multiple letter substring (OSC, JMP, SMTH etc)

Try the following...

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 num1 As Long
    Dim num2 As Long
    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)
            num1 = GetNumericValue(arr(i))
            num2 = GetNumericValue(arr(j))
            If num1 > num2 Then
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
   
End Sub

Function GetNumericValue(ByVal str As String) As Variant

    Dim i As Long
   
    For i = 1 To Len(str)
        If IsNumeric(Mid(str, i, 1)) Then
            GetNumericValue = CLng(Mid(str, i))
            Exit Function
        End If
    Next i
   
    GetNumericValue = CVErr(xlErrValue)
   
End Function

Here are some examples...

fionut.xlsm
ABC
1DataResult
2DZ1, DZ100, DZ102, DZ22DZ1, DZ22, DZ100, DZ102<--- ok
3OSC1, OSC100, OSC102, OSC22OSC1, OSC22, OSC100, OSC102<--- ok
4SMTH1, SMTH100, SMTH102, SMTH22SMTH1, SMTH22, SMTH100, SMTH102<--- ok
5DZ1, DZ100, DZ, DZ22#VALUE!<-- error due to missing number
6800, 2, 600, 80, 4002, 80, 400, 600, 800<-- ok
7DZ1, DZ100, OSC22, SMTH102DZ1, OSC22, DZ100, SMTH102<-- wrong due to unsupported mixed type
Sheet1
Cell Formulas
RangeFormula
B2:B7B2=SortCSVString(A2, ",")


Hope this helps!
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top