Microsoft Office Forums - View Single Post - [Solved] Split function by comma and hyphen simultaneously

Guessed · 08-15-2021, 04:02 AM

Before you create nSplitItem, you should call a function to tidy the string and expand the number ranges. Something like this should work

Code:

Sub TestFun()
  MsgBox funExpandDashes("1-3,5,10-15")
End Sub

Function funExpandDashes(strPage As String) As String
  Dim arrOuter() As String, iMin As Integer, iMax As Integer, arrInner() As String, sExpand As String
  Dim x As Integer, y As Integer

  strPage = Replace(strPage, " ", "")     'make sure there are no spaces
  arrOuter = Split(strPage, ",")
  For x = LBound(arrOuter) To UBound(arrOuter)
    arrInner = Split(arrOuter(x), "-")
    If UBound(arrInner) > LBound(arrInner) Then
      sExpand = arrInner(0)
      For y = CInt(arrInner(0)) + 1 To CInt(arrInner(1))
        sExpand = sExpand & "," & y
      Next y
      arrOuter(x) = sExpand
    End If
  Next x
  funExpandDashes = Join(arrOuter, ",")
  
  'Add code here to make sure order is increasing (ie sort array)
  'Add code here to remove duplicates
 
End Function

Note the two suggestions to make this more robust and reduce user data entry issues