VBA Excel Split Function to delimit text

 What does VBA Excel Split Function do?

VBA Excel Split Function separates a text by delimiter into an array, for example, you can split a text a;b;c;d into array {a,b,c,d}, afterwards you can get the desired array item (starting from 0). Split Function is similar to the action of “Text to Columns”.

Syntax of VBA Excel Split Function

text text to be delimited
delimiter Optional. String character used to identify substring limits. If omitted, the space character (” “) is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing the entire expression string is returned.
limit Optional. Number of substrings to be returned; –1 indicates that all substrings are returned.
compare Optional. Type of comparison to perform. It can be one of the following values:

VBA Constant Value Explanation
vbUseCompareOption -1 Default value, uses setting of Option Compare statement defined at Module level.(Two options: Option Compare Text / Option Compare Binary)If you don’t write Option Compare statement, Option Compare Binary is default
vbBinaryCompare 0 Binary comparison, turn text into a sequence to compare. Since the sequence is case sensitive, you can compare text using this option if you want the comparison to be case sensitive, which means you cannot search “aaa” within “AAA”
vbTextCompare 1 Compare text, case insensitive, which means you can search “aAa” within “AAA”

Example of Excel VBA Split Function

VBA Code Result Explanation
Split(“Mary Ann”, ” “)(0) Mary Return the first array item of split (array item starts from 0)
Split(“Mary Ann”, ” “)(1) Ann Return the second array item of split
Split($A$1,”$”)(1) A Get the column name of Cell address
Split(Cells(1, column_num).Address, “$”)(1) Convert numeric column number (1,2,3) to column name (A,B,C)

The below code assign an array name arr() to the split array, and then loop through all array items to count number of “Mary” (case insensitive)

Public Sub convtArray1()
    Dim arr() As String
    arr() = Split("Mary Ann MARY", " ")
    For i = 0 To UBound(arr())
        If UCase(arr(i)) = "MARY" Then
            Count = Count + 1
        End If
    Next i
    MsgBox (Count)
End Subb

Outbound References


Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *