# 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

### Search for Tips

### All Tips

### Browse Tips by Category

### Tip Books

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

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

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