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)
        ExtractElement = ""
    End If
End Function

The formula below demonstrates how the ExtractElement function can be used in a formula.


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) & _
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

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

© Copyright 2018, J-Walk & Associates, Inc.
Privacy Policy