How to use VBA Split Function

VBA Split Function Definition and Syntax with Example
Split a one of the String function that can split a text string into an array, by making use of a delimiter character. It returns a zero-based, one-dimensional array holding the parts of the original text string.

Syntax :
Split (Text_String, Delimiter, Limit, Compare)

Here, ‘Text_String’ refers to the string that you need to break.

'Delimiter' refers to the character which divides the string into parts. This is an optional argument, space character “ ” is treated as the default delimiter.

‘Limit’ is an optional parameter. It specifies the maximum number of parts into which the input string should be divided. The default value is -1, which means that the string should be divided at each occurrence of ‘Delimiter’.

‘Compare’ is also an optional argument that specifies the comparison method to be used while evaluating the strings. 'Compare’ argument can have anyone of the below values:

Key characteristics of a Split Function:
Split Function returns a String Array and not a String.If you omit the ‘compare’ argument then, Split Statement uses the Binary comparison method as default.
If the ‘Text_String’ that you pass to Split is a zero length string then, it returns a single-element array containing a zero-length string.
If the ‘Delimiter’ is not found anywhere in the ‘Text_string’, then Split returns the single-element array containing 'Text_String', as it is.

Example :
Sub Split_Store_In_Array()
Dim WS As Worksheet
Dim MyStr() As String
Dim Str As String

Set WS = ActiveSheet
Str = "This_Is_What_I_Want_To_Store"
'Storing the String in an Array by Spliting it by delimiter'_'
MyStr() = Split(Str, "_")
'After Spliting the String it will store in Array as follows
MyStr(0) = "This"
MyStr(1) = "Is"
MyStr(2) = "What"
MyStr(3) = "I"
MyStr(4) = "Want"
MyStr(0) = "To"
MyStr(0) = "Store"

'Looping through Array to Display the Stored values.
For x = LBound(MyStr) To UBound(MyStr)
MsgBox MyStr(x)
Next x

End Sub

------------------------------------------------------------------------------------------------------------------

Suppose if you specifies the 'Limit' , the maximum number of parts into which the input string should be divided as follows :

MyStr() = Split(Str, "_" , 2)

Now the String is Split into 2 Parts , string up to first occurrence of Delimiter '_' is One part and remaining part of the string is Second part.

Str = "This_Is_What_I_Want_To_Store"

MyStr(0) = "This"
MyStr(1) = "Is_What_I_Want_To_Store"

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------