Sort By Only The Numbers In Front Of The Commas

I have a list of numbers in a column as follows:

2
55
37
8
22, 17
3, 12, 47

When I sort with my macro I get:

2
8
37
55
3, 12, 47
22, 17

What I want is:

2
3, 12, 47
8
22, 17
37
55

Is there a way in vba to have it sort the list using only the number in front of the first comma?
Thank you.

I would copy and paste the data from column 1 to column 2. Use the text to columns function and use the comma as the delimiter. Sort on column 2.

Originally Posted by AlKey

Try this formula in B2

=IFERROR(--LEFT(A2,FIND(",",A2)-1),A2)

then filter by column B

	A	B
1	Range	Filter
2	2	2
3	3, 12, 47	3
4	8	8
5	22, 17	22
6	37	37
7	55	55

Thanks, that works. I had to have vba put the formula into the cell because other code comes first and sorts and deletes rows in the table first. But it works great. Thanks again.

If you wanted to do the whole thing (including the sort) in VBA without using a helper column this works:

Option Explicit
Option Base 1

Sub SortInVB()

Dim vSortArr As Variant
Dim lUpperBnd As Long

Dim n As Long


  vSortArr = Range("A1:a6")
  lUpperBnd = UBound(vSortArr)
  ReDim Preserve vSortArr(1 To lUpperBnd, 1 To 2)

  For n = 1 To lUpperBnd
    If Left(vSortArr(n, 1), InStr(vSortArr(n, 1), ",")) = "" Then
      vSortArr(n, 2) = vSortArr(n, 1)
    Else
      vSortArr(n, 2) = CLng(Left(vSortArr(n, 1), InStr(vSortArr(n, 1), ",") - 1))
    End If
  Next n
  
  vSortArr = BubbleSort2d2cArray(vSortArr, 2)
  ReDim Preserve vSortArr(1 To lUpperBnd, 1)
  Range("a1:a6").Value = vSortArr
  
 
End Sub

Private Function BubbleSort2d2cArray(SortArray As Variant, _
                                     Optional SortColumn As Long) As Variant
'Code was adapted from MSDN KB Article ID: 133135.
'Using a Visual Basic Macro to Sort Arrays in Microsoft Excel," found at:
'http://support.microsoft.com/kb/133135

Dim vTempArr(1 To 1, 1 To 2) As Variant
Dim bSwitch As Boolean
Dim lLowerBnd As Long
Dim lUpperBnd As Long
Dim i As Long
  
  lLowerBnd = LBound(SortArray)
  lUpperBnd = UBound(SortArray)
  If SortColumn <> 2 Then SortColumn = 1
  
  Do
    bSwitch = False
    For i = lLowerBnd To lUpperBnd - 1
      If SortArray(i, SortColumn) > SortArray(i + 1, SortColumn) Then
        bSwitch = True
        vTempArr(1, 1) = SortArray(i, 1)
        vTempArr(1, 2) = SortArray(i, 2)
        SortArray(i, 1) = SortArray(i + 1, 1)
        SortArray(i, 2) = SortArray(i + 1, 2)
        SortArray(i + 1, 1) = vTempArr(1, 1)
        SortArray(i + 1, 2) = vTempArr(1, 2)
      End If
    Next i
    Loop While bSwitch
  
  BubbleSort2d2cArray = SortArray

  
End Function

Regards,

Tom