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

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

Public Sub DisplayCommaSep(ByVal sConCat As String) 
Dim aArray As Variant 
Dim sDisplay As String 
Dim iCount As Integer 

   aArray = VBA.Split(sConCat, ",") 
   For iCount = LBound(aArray) To UBound(aArray) 
      sDisplay = sDisplay & aArray(iCount) 
      If (iCount < UBound(aArray)) Then 
         sDisplay = sDisplay & vbNewLine 
      End If 
   Next iCount 
   Call MsgBox(sDisplay) 
End Sub