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


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