{"id":12190,"date":"2024-02-02T22:55:08","date_gmt":"2024-02-02T22:55:08","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/wp\/?p=3299"},"modified":"2024-02-02T22:55:08","modified_gmt":"2024-02-02T22:55:08","slug":"split-function-excel-vba","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/","title":{"rendered":"Excel VBA – What is SPLIT Function?"},"content":{"rendered":"

S<\/span>plit 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.<\/p>\n

\"SPLIT-function\"

SPLIT-function<\/p><\/div>\n

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.<\/strong><\/p>\n

Split() Function<\/h1>\n

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<\/i><\/strong>. If delimiter is not found (even once) then the whole string will be returned by this function.<\/p>\n

Syntax:<\/h2>\n

This is how Syntax will look like as soon as you type VBA.Split <\/strong> in VB Editor
\n\"SPLIT<\/p>\n

Split(<YourString>, <Delimiter>, <Limit>, <CompareMethod>)<\/h3>\n

Where:<\/h2>\n

YourString (Mandatory) :<\/h2>\n

This is the string which has to be split.<\/p>\n

Delimiter (Optional) :<\/h2>\n

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.<\/p>\n

Limit (Optional) :<\/h2>\n

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.<\/p>\n

CompareMethod (Optional) :<\/h2>\n

This is a Numeric value ( 0 or 1) to indicate which method to use for comparison. 0<\/strong> is for performing a binary comparison. 1<\/strong> is for performing a textual comparison.<\/p>\n

Examples:<\/h1>\n

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 \ud83d\ude42<\/p>\n

<\/i>\u00a0 Example 1 : Omitting all the Optional Parameters<\/a><\/h2>\n

<\/i>\u00a0 Example 2 : What is Limit exactly? and How it works?<\/a><\/h2>\n

<\/i>\u00a0 Example 3 : What is difference between Binary and Text comparison?<\/a><\/h2>\n

<\/i>\u00a0 Special Case in SPLIT Function<\/a><\/h2>\n

<\/a><\/p>\n

Example 1 : Omitting all the Optional Parameters<\/h1>\n

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

String : <\/strong> My Name Is Vishwamitra Mishra<\/i>
\nBelow is the function with my string.
\nVBA.Split(“My Name Is Vishwamitra Mishra”)<\/strong>
\nFollowing is the Array which is returned by the above VBA statement (refer the below picture)
\n\"SPLIT<\/p>\n

Explanation:<\/h2>\n

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.
\nDelimiter:<\/strong> As mentioned in theory above, default delimiter is considered as ” ” (space) when omitted hence space is used as delimiter.
\nLimit:<\/strong>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<\/i> (” “) found in the string.
\nCompareMethod :<\/strong>By default it does the text comparison. Hence 1<\/strong> is passed.
\nNow 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:<\/strong><\/p>\n

\"Split<\/p>\n

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.
\n<\/a><\/p>\n

Example 2 : What is difference between Binary and Text comparison?<\/h1>\n

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.
\nFor example: <\/strong> Upper case – A<\/strong> and Lower case – a<\/strong>. Both “A” and “a” has different unicode value but as a text both are same.<\/p>\n

Refer the below images which shows the difference in both type of comparison.<\/p>\n

With Binary Comparison – Same string, Same delimiter and same Limit<\/h2>\n

\"Split-Binary-Comparison\"<\/p>\n

With Text Comparison – Same string, Same delimiter and same Limit<\/h2>\n

\"Split-Text-Comparison\"<\/p>\n

\n

Important Note:<\/h2>\n

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<\/strong> is not equal to and<\/strong> but in text comparison they are equal.<\/p>\n<\/div>\n

<\/a><\/p>\n

Example 3: What is Limit in SPLIT() function exactly ?<\/h1>\n

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)<\/p>\n

Let’s take the below parameters for the VBA.Split function and see how it behaves:
\nString: <\/strong> My,Name,Is,Vishwamitra,Mishra
\nDelimiter:<\/strong> comma (,)
\nLimit:<\/strong> 2
\nCompare:<\/strong> Default<\/p>\n

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:<\/p>\n

\"Split-Function-Limit-Explanation\"<\/p>\n

<\/a><\/p>\n

Special Case in SPLIT Function<\/h1>\n

Split function behaves little different when occurences of delimiter is consecutive in a row<\/strong> or at the start of the string<\/strong> or at the end of the string.<\/strong>
\nIn 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:
\n\"Split-Special-Case\"<\/p>\n

Explanation:<\/h2>\n

1. First Array item is shown as an Empty string because of the first occurrence of the delimiter comma (,)
\n2. Third item of the result array is again and empty string because of 2 consecutive occurrence of the delimiter comma (,)
\n3. 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.<\/p>\n

<\/i> Have you got any question or doubt ?<\/h1>\n

Do comment in this article or mail me. I will try my best to clarify answer your queries as soon as possible.
\nYou can also tweet your query or post your query on my Facebook page for quick response.<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"

plit 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. Here in this article, you will learn about splitting the data\/string which is […]<\/p>\n","protected":false},"author":45,"featured_media":14195,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1679,1678,5204],"tags":[],"yoast_head":"\nExcel VBA - What is SPLIT Function? - Let's excel in Excel<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel VBA - What is SPLIT Function?\" \/>\n<meta property=\"og:description\" content=\"plit 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. Here in this article, you will learn about splitting the data\/string which is […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2024-02-02T22:55:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/05\/SPLI-function.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"590\" \/>\n\t<meta property=\"og:image:height\" content=\"421\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Excel VBA – What is SPLIT Function?\",\"datePublished\":\"2024-02-02T22:55:08+00:00\",\"dateModified\":\"2024-02-02T22:55:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/\"},\"wordCount\":1110,\"commentCount\":8,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Macro Beginner\",\"Interesting VBA Functions\",\"VBA Programming\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/\",\"name\":\"Excel VBA - What is SPLIT Function? - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2024-02-02T22:55:08+00:00\",\"dateModified\":\"2024-02-02T22:55:08+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Macro Beginner\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-macro-beginner\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Excel VBA – What is SPLIT Function?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Excel VBA - What is SPLIT Function? - Let's excel in Excel","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/","og_locale":"en_US","og_type":"article","og_title":"Excel VBA - What is SPLIT Function?","og_description":"plit 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. Here in this article, you will learn about splitting the data\/string which is […]","og_url":"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2024-02-02T22:55:08+00:00","og_image":[{"width":590,"height":421,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/05\/SPLI-function.jpg","type":"image\/jpeg"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Excel VBA – What is SPLIT Function?","datePublished":"2024-02-02T22:55:08+00:00","dateModified":"2024-02-02T22:55:08+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/"},"wordCount":1110,"commentCount":8,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Macro Beginner","Interesting VBA Functions","VBA Programming"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/","url":"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/","name":"Excel VBA - What is SPLIT Function? - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2024-02-02T22:55:08+00:00","dateModified":"2024-02-02T22:55:08+00:00","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/split-function-excel-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Macro Beginner","item":"https:\/\/vmlogger.com\/excel\/excel-macro-beginner\/"},{"@type":"ListItem","position":3,"name":"Excel VBA – What is SPLIT Function?"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12190"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12190"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12190\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/14195"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12190"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12190"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12190"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}