Split is the hardest shot to hit in Bowling. But unlike bowling SPLIT is very simple function to use in excel VBA. Splitting data or string in VBA, sometimes becomes necessary to organize it and to put it in more readable format.

SPLIT-function

Here in this article, you will learn about splitting the data/string which is joined with a specific delimiter. Delimiter could be any character or special character. This VBA function basically finds each occurrence of the delimiter and split it and keep storing all the split sub-string in an array. Therefore this VBA function returns a String Array.

Split() Function

This is a VBA function not a Worksheet function (Excel formula). This function returns a one dimensional string array having all the strings split by provided delimiter. If delimiter is not found (even once) then the whole string will be returned by this function.

Syntax:

This is how Syntax will look like as soon as you type VBA.Split in VB Editor

Split(<YourString>, <Delimiter>, <Limit>, <CompareMethod>)

Where:

YourString (Mandatory) :

This is the string which has to be split.

Delimiter (Optional) :

Delimiter text by which above text has to be split. For example: Comma (,), Hyphen (-), etc. If this parameter is omitted then Space (” “) is considered as default delimiter.

Limit (Optional) :

This is the maximum number of split in which original text has to be split. The default value is -1 which indicates to show all the split values.

CompareMethod (Optional) :

This is a Numeric value ( 0 or 1) to indicate which method to use for comparison. 0 is for performing a binary comparison. 1 is for performing a textual comparison.

Examples:

With the above theory you may not be more comfortable until you practice with some example by running the VBA code. I have taken few possible cases with different parameters in the below examples. They will make you more comfortable in using this function 🙂

  Example 1 : Omitting all the Optional Parameters

  Example 2 : What is Limit exactly? and How it works?

  Example 3 : What is difference between Binary and Text comparison?

  Special Case in SPLIT Function

Example 1 : Omitting all the Optional Parameters

In the below Example, I have omitted all the optional parameter and passed the String ONLY which has to be split because that is the only parameter which is mandatory in this function.

String : My Name Is Vishwamitra Mishra
Below is the function with my string.
VBA.Split(“My Name Is Vishwamitra Mishra”)
Following is the Array which is returned by the above VBA statement (refer the below picture)

Explanation:

In the above code all the optional parameters are omitted. Hence using all the default values for other parameters, this function has returned this array.
Delimiter: As mentioned in theory above, default delimiter is considered as ” ” (space) when omitted hence space is used as delimiter.
Limit:As mentioned above, by default this function split the whole string for all the occurrences of the delimiter found. Hence it has split the whole string with all the delimiter Sapce (” “) found in the string.
CompareMethod :By default it does the text comparison. Hence 1 is passed.
Now in the above string if I use comma (,) as a delimiter instead of Space (” “) then the whole string was returned as a single split because the default delimiter which is space (” “), not found in the main string as shown in the below image:

To split the above string you should pass delimiter as comma (,) then it will split the string in to sub-string and put it in an array. In the next example I have used the delimiter as comma (,) to split.

Example 2 : What is difference between Binary and Text comparison?

It is very simple. As the name suggests in binary comparison, system compares the Numeric unicode value of each character. But in Text Comparison, comparison is based on the reference of the current world definition.
For example: Upper case – A and Lower case – a. Both “A” and “a” has different unicode value but as a text both are same.

Refer the below images which shows the difference in both type of comparison.

With Binary Comparison – Same string, Same delimiter and same Limit

With Text Comparison – Same string, Same delimiter and same Limit

Example 3: What is Limit in SPLIT() function exactly ?

Actually limit is the number of split this function has to do of the main string. In other words you can say this is the upper limit of the array which is returned from this function (If it is set other than the default limit which is -1)

Let’s take the below parameters for the VBA.Split function and see how it behaves:
String: My,Name,Is,Vishwamitra,Mishra
Delimiter: comma (,)
Limit: 2
Compare: Default

As I explained above since limit is set as 2 hence Split function will find the first occurrences of the delimiter i.e. comma (,) and put it as first items of the array and rest of the string (though there are occurrences of comma (,)) will be put as 2nd item of the split array. Refer the below picture:

Special Case in SPLIT Function

Split function behaves little different when occurences of delimiter is consecutive in a row or at the start of the string or at the end of the string.
In all the above 3 cases, split function interprets them as they are surrounded by an empty string. Hence in all the above cases an empty string will be sent in the result array. refer the below image as shown:

Explanation:

1. First Array item is shown as an Empty string because of the first occurrence of the delimiter comma (,)
2. Third item of the result array is again and empty string because of 2 consecutive occurrence of the delimiter comma (,)
3. The last item of the result array is again an empty string because of the last occurrence of the delimiter at the end of the string.

Have you got any question or doubt ?

Do comment in this article or mail me. I will try my best to clarify answer your queries as soon as possible.
You can also tweet your query or post your query on my Facebook page for quick response.