Excel VBA – What is SPLIT Function?

.

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

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 - FUNCTION - SYNTAX

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)
SPLIT - All-Default-Options

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:

Split - Default-Parameter

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

Split-Binary-Comparison

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

Split-Text-Comparison

Important Note:

With the above example difference between Binary Comparison and Text Comparison is clear. Though, rest all the parameters are same in both the statement but still result is different because comparison method is different. Reason is that in Binary Comparison AND is not equal to and but in text comparison they are equal.

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:

Split-Function-Limit-Explanation

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:
Split-Special-Case

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.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

4 Comments

  1. prajith lakhraj

    hi Vishwa,

    great stuff, i really enjoyed your explanation about split function, but how would you do the reverse.
    eg. A .pdf document that you would like to copy over to excel in the same page format, how is this possible, as, when you copy from .pdf to excel, it just pastes it in one column, yet in .pdf it probably had 5 different columns.

    rgds,
    Prajith

    Reply
  2. Andrew

    Hello and thanks for the great post. Is there a way to split only at the LAST occurrence of the delimiter?

    Reply
  3. Tarun Aggarwal

    Hi,

    I am looking for some split code that can separate my Input A/B/C/D/E as A in column A, B in column B & so on where A,B etc are 1 or 2 digit numbers.

    Eg. 5/40/25/25/5

    Also it would be great if you can guide me how to use the code in excel to work.

    Thanks,
    Tarun

    Reply
  4. Almedina

    I would love to learn VBA. I have just macro’s, but not gone back & edit them like you did with the number faromt. That was cool.Thanks

    Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro SPLITING data in Excel Without VBA Code - [...] my previous article as you learnt how to split data using VBA SPLIT() function. In this Article I am…
  2. VBA Guide to Interact with Text Files – Part – 2 of 2 - Welcome to LearnExcelMacro.com - […] As the name suggests, using this statement you can read the whole line at once and it gets stored…
  3. UDF to Convert Numbers to Letters - Spell Currency in Words - […] it re-usable and simplify my previous code to convert numbers to letters, I have made a logical split of…
  4. UDF to Convert Numbers to Letters - Spell Currency in Words - […] it re-usable and simplify my previous code to convert numbers to letters, I have made a logical split of…

Submit a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest