Splitting

LEFT(string, length)

You can use the LEFT function to return a number of characters from the left of a string.

Left("sometext", 4) = "some" 
Left("sometext", 20) = "sometext" 

RIGHT(string, length)

You can use the RIGHT function to return a number of characters from the right of a string.

Right("sometext", 4) = "text" 
Right("sometext", 20) = "sometext" 

MID(string, start [,length])

You can use the MID function to return the text which is a substring of a larger string.
The first character position is 1.

Mid("C:\Temp\",1) = "C:\Temp\" 
Mid("C:\Temp\",3) = "\Temp\" 
Mid("C:\Temp\",3,1) = "\" 

SPLIT(expression [,delimiter] [,limit] [,compare])

You can use the SPLIT function to return an array containing a specified number of substrings (Variant).

Dim aValues As Variant 
Dim sStringConcat As String 
sStringConcat = "one,two,three" 
aValues = Split(sStringConcat, ",") = {"one","two","three"} 

Splitting at the last backslash

Dim sFullPath As String 
Dim sSubFolder as String 
sFullPath = "C:\temp\myfolder" 
sSubFolder = Right(sFullPath, Len(sFullPath) - InStrRev(sFullPath, "\")) 
Debug.Print sSubFolder   '"myfolder"  

Splitting at the first occurrence

Public Function ReturnFirstElement(ByVal sConCat As String, _ 
                                   ByVal sSeparatorChar As String) _ 
                                   As String 
Dim ipos As Integer 
    ipos = VBA.InStr(sConCat, sSeparatorChar) 
    ReturnFirstElement = VBA.Left(sConCat, ipos - 1) 
End Function 

Splitting at the last occurrence

Public Function ReturnLastElement(ByVal sConCat As String, _ 
                                  ByVal sSeparatorChar As String) _ 
                                  As String 
Dim ipos As Integer 
    ipos = VBA.InStrRev(sConCat, sSeparatorChar) 
    ReturnLastElement = VBA.Right(sConCat, Len(sConCat) - ipos) 
End Function 

Splitting at the nth occurrence

Public Function ReturnNthElement(ByVal sConCat As String, _ 
                                 ByVal sSeparatorChar As String, _ 
                                 ByVal iNumber As Integer) _ 
                                 As String 
Dim arArray As Variant 
   arArray = VBA.Split(sConCat, sSeparatorChar) 
   ReturnNthElement = arArray(iNumber - 1) 
End Function 

Splitting between two search terms

Public Sub Testing() 
Dim sText As String 
   sText = "Be More Productive Using Better Solutions" 
   
   Debug.Print Trim(Split(Split(sText, "More")(1), "Better")(0)) 
   Debug.Print ReturnBetweenElements(sText, "More", "Better") 
End Sub 

Public Function ReturnBetweenElements(ByVal sConCat As String, _ 
                                      ByVal sFirstElement As String, _ 
                                      ByVal sSecondElement As String) _ 
                                      As String 
Dim arArray1 As Variant 
Dim arArray2 As Variant 
Dim sReturn As String 
Dim sElement1 As String 
Dim sElement2 As String 

   arArray1 = VBA.Split(sConCat, sFirstElement)           'removes the first element and creates an array of before and after  
   sElement1 = arArray1(1)                                'returns the string after the first element  

   arArray2 = VBA.Split(sElement1, sSecondElement)        'removes the second element and create an array of before and after  
   sElement2 = arArray2(0)                                'returns the string before the second element  

   ReturnBetweenElements = Trim(sElement2) 
End Function 

Splitting a comma separated list

Looping through the array.

Public Sub Testing() 
   Call SplitCommaSep1("one,two,three,four,five,six") 
End Sub 

Public Sub SplitCommaSep1(ByVal sConCat As String) 
Dim avStringSplit As Variant 
Dim lCount As Long 
Dim snextentry As String 

   avStringSplit = VBA.Split(sConCat, ",") 
   For lCount = LBound(avStringSplit) To UBound(avStringSplit) 
      snextitem = avStringSplit(lCount) 
      Debug.Print snextentry 
   Next lCount 
End Sub 

Splitting a comma separated list

Using a For Each loop.

Public Sub Testing() 
   Call SplitCommaSep2("one,two,three,four,five,six") 
End Sub 

Public Sub SplitCommaSep2(ByVal sConCat As String) 
Dim asStringSplit() As String 
Dim vItem As Variant 
Dim snextentry As String 

   asStringSplit = VBA.Split(sConCat, ",") 
   For Each vItem In asStringSplit 
       snextentry = CStr(vItem) 
       Debug.Print snextentry 
   Next item 
End Sub 

Splitting a comma separated list

Using string manipulation.

Public Sub Testing() 
   Call SplitCommaSep3("one,two,three,four,five,six") 
End Sub 

Public Sub SplitCommaSep3(ByVal sConCat As String) 
Dim snextentry As String 

   Do While (Len(sConCat) > 0) 
      If (InStr(1, sConCat, ",") = 0) Then 
         snextentry = sConCat 
         sConCat = "" 
      Else 
         snextentry = Left(sConCat, InStr(1, sConCat, ",") - 1) 
         sConCat = Right(sConCat, Len(sConCat) - Len(snextentry) - 1) 
      End If 
      Debug.Print snextentry 
   Loop 
End Sub