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
Split(text,[delimiter],[limit],[compare])
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:
|
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
https://msdn.microsoft.com/en-us/library/6x627e5f%28v=vs.90%29.aspx