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

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext