The Versatile Split Function
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.
This formula returns 909, the third element in the string (which uses a "-" as the delimiter).
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
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
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