The Versatile Split Function

Category: VBA Functions | [Item URL]

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).

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