The Versatile Split Function
VBA's Split function, introduced with Excel 2000, can simplify many programming tasks. This function accepts a text string, and returns a zero-based variant array that contains the elements of the string (you specify the character that delimits the elements).
A simple example
The procedure below demonstrates how the Split function works.
Sub SplitDemo()
Dim txt As String
Dim x As Variant
Dim i As Long
txt = "The Split function is versatile"
x = Split(txt, " ")
For i = 0 To UBound(x)
Debug.Print x(i)
Next i
End Sub
This procedures displays the output shown below.
In this case, the delimiter is a space character. You can specify any character or string to be used as the delimiter. The following examples demonstrate some other uses for the Split function.
Extracting an element
Split is a VBA function, so it can't be used in a worksheet formula. The function below is simply a "wrapper" for the Split function, so your formulas can make use of this handy function.
Function ExtractElement(str, n, sepChar)
' Returns the nth element from a string,
' using a specified separator character
Dim x As Variant
x = Split(str, sepChar)
If n > 0 And n - 1 <= UBound(x) Then
ExtractElement = x(n - 1)
Else
ExtractElement = ""
End If
End Function
The formula below demonstrates how the ExtractElement function can be used in a formula.
=ExtractElement("546-339-909-944",3,"-")
This formula returns 909, the third element in the string (which uses a "-" as the delimiter).
Refer to a previous tip for a similar function that does not require the Split function.
Counting words
The function below returns the number of words in a string. It uses Excel's TRIM function to remove excess spaces (which would cause an incorrect result).
Function WordCount(txt) As Long
' Returns the number of words in a string
Dim x As Variant
txt = Application.Trim(txt)
x = Split(txt, " ")
WordCount = UBound(x) + 1
End Function
Splitting up a filename
The two examples in this section make it easy to extract a path or a filename from a full filespec, such as "c:\files\workbooks\archives\budget98.xls"
Function ExtractFileName(filespec) As String
' Returns a filename from a filespec
Dim x As Variant
x = Split(filespec, Application.PathSeparator)
ExtractFileName = x(UBound(x))
End Function
Function ExtractPathName(filespec) As String
' Returns the path from a filespec
Dim x As Variant
x = Split(filespec, Application.PathSeparator)
ReDim Preserve x(0 To UBound(x) - 1)
ExtractPathName = Join(x, Application.PathSeparator) & _
Application.PathSeparator
End Function
Using the filespec shown above as the argument, ExtractFileName returns "budget98.xls" and ExtractPathName returns "c:\files\workbooks\archives\"
Counting specific characters in a string
The function below accepts a string and a substring as arguments, and returns the number of times the substring is contained in the string.
Function CountOccurrences(str, substring) As Long
' Returns the number of times substring appears in str
Dim x As Variant
x = Split(str, substring)
CountOccurrences = UBound(x)
End Function
Finding the longest word
The function below accepts a sentence, and returns the longest word in the sentence.
Function LongestWord(str) As String
' Returns the longest word in a string of words
Dim x As Variant
Dim i As Long
str = Application.Trim(str)
x = Split(str, " ")
LongestWord = x(0)
For i = 1 To UBound(x)
If Len(x(i)) > Len(LongestWord) Then
LongestWord = x(i)
End If
Next i
End Function