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