{"id":25998,"date":"2018-05-18T01:12:02","date_gmt":"2018-05-17T19:42:02","guid":{"rendered":"https:\/\/trumpexcel.com\/?p=25998"},"modified":"2023-09-29T15:20:25","modified_gmt":"2023-09-29T09:50:25","slug":"vba-split-function","status":"publish","type":"post","link":"https:\/\/trumpexcel.com\/vba-split-function\/","title":{"rendered":"Excel VBA Split Function &#8211; Explained with Examples"},"content":{"rendered":"<p>When working with VBA in Excel, you may have a need to split a string into different parts based on a delimiter.<\/p>\n<p>For example, if you have an address, you can use the VBA Split function to get different parts of the address that are separated by a comma (which would be the delimiter in this case).<\/p>\n<p>SPLIT is an inbuilt string function in Excel VBA that you can use to split a text string based on the delimiter.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">This Tutorial Covers:<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/trumpexcel.com\/vba-split-function\/#Excel-VBA-SPLIT-Function-%E2%80%93-Syntax\" >Excel VBA SPLIT Function &#8211; Syntax<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/trumpexcel.com\/vba-split-function\/#Example-1-%E2%80%93-Split-the-Words-in-a-Sentence\" >Example 1 &#8211; Split the Words in a Sentence<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/trumpexcel.com\/vba-split-function\/#Example-2-%E2%80%93-Count-the-Number-of-Words-in-a-Sentence\" >Example 2 &#8211; Count the Number of Words in a Sentence<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/trumpexcel.com\/vba-split-function\/#Example-3-%E2%80%93-Using-a-Delimiter-Other-than-Space-Character\" >Example 3 &#8211; Using a Delimiter Other than Space Character<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/trumpexcel.com\/vba-split-function\/#Example-4-%E2%80%93-Divide-an-Address-into-three-parts\" >Example 4 &#8211; Divide an Address into three parts<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/trumpexcel.com\/vba-split-function\/#Example-5-%E2%80%93-Get-the-City-Name-from-the-Address\" >Example 5 &#8211; Get the City Name from the Address<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n\n<h2><span class=\"ez-toc-section\" id=\"Excel-VBA-SPLIT-Function-%E2%80%93-Syntax\"><\/span>Excel VBA SPLIT Function &#8211; Syntax<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<pre>Split ( Expression, [Delimiter], [Limit], [Compare] )<\/pre>\n<ul>\n<li><strong>Expression:<\/strong> This is the string that you want to split based on the delimiter. For example, in case of the address example, the entire address would be the &#8216;expression&#8217;. In case this is a zero-length string (&#8220;&#8221;) SPLIT function would return an empty array.<\/li>\n<li><strong>Delimiter:<\/strong> This is an optional argument. This is the delimiter that is used to split the &#8216;Expression&#8217; argument. In case of our address example, a comma is a delimiter that is used to split the address into different parts. If you don&#8217;t specify this argument, a space character is considered the default delimiter. In case you give a zero-length string (&#8220;&#8221;), the entire &#8216;Expression&#8217; string is returned by the function.<\/li>\n<li><strong>Limit:<\/strong> This is an optional argument. Here you specify the total number of substrings that you want to return. For example, if you only want to return the first three substrings from the &#8216;Expression&#8217; argument, this would be 3. If you don&#8217;t specify this argument, the default is -1, which returns all the substrings.<\/li>\n<li><strong>Compare:<\/strong> This is an optional argument. Here you specify the type of comparison you want the SPLIT function to perform when evaluating the substrings. The following options are available:\n<ul>\n<li><strong>When Compare is 0<\/strong>: This is a Binary comparison. This means that if your delimiter is a text string (let&#8217;s say ABC), then this would be case-sensitive. &#8216;ABC&#8217; would not be equal to &#8216;abc&#8217;.<\/li>\n<li><strong>When Compare is 1<\/strong>:\u00a0This is a Text comparison. This means that if your delimiter is a text string (let&#8217;s say ABC), then even if you have &#8216;abc&#8217; in the &#8216;Expression&#8217; string, it would be considered as a delimiter.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Now that we have covered the basics of the SPLIT function, let&#8217;s see a few practical examples.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example-1-%E2%80%93-Split-the-Words-in-a-Sentence\"><\/span>Example 1 &#8211; Split the Words in a Sentence<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Suppose I have the text &#8211; &#8220;The Quick Brown Fox Jumps Over The Lazy Dog&#8221;.<\/p>\n<p>I can use the SPLIT function to get each word of this sentence into as a separate item in an array.<\/p>\n<p>The below code would to this:<\/p>\n<pre>Sub SplitWords()\nDim TextStrng As String\nDim Result() As String\nTextStrng = \"The Quick Brown Fox Jumps Over The Lazy Dog\"\nResult() = Split(TextStrng)\nEnd Sub<\/pre>\n<p>While the code does nothing useful, it will help you understand what the Split function in VBA does.<\/p>\n<p>Split function splits the text string and assigns each word to the Result array.<\/p>\n<p>So in this case:<\/p>\n<ul>\n<li>Result(0) stores the value &#8220;The&#8221;<\/li>\n<li>Result(1) stores the value &#8220;Quick&#8221;<\/li>\n<li>Result(2) stores the value &#8220;Brown&#8221; and so on.<\/li>\n<\/ul>\n<p>In this example, we have only specified the first argument &#8211; which is the text to be split. Since no delimiter has been specified, it takes space character as the default delimiter.<\/p>\n<div class=\"cbyellow\">\n<p><strong>Important Note:<\/strong><\/p>\n<ol>\n<li>VBA\u00a0SPLIT function returns an array that starts from base 0.<\/li>\n<li>When the result of the SPLIT function is assigned to an array, that array must be declared as a String data type. If you declare it as a Variant data type, it will show a type mismatch error).\u00a0 In the example above, note that I have declared Result() as a String data type.<\/li>\n<\/ol>\n<pre>Also read:\u00a0<a href=\"https:\/\/trumpexcel.com\/split-text-to-rows-excel\/\" target=\"_blank\" rel=\"noopener\">Split Text into Different Rows in Excel<\/a><\/pre>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Example-2-%E2%80%93-Count-the-Number-of-Words-in-a-Sentence\"><\/span>Example 2 &#8211; Count the Number of Words in a Sentence<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>You can use the SPLIT function to get the total number of words in a sentence. The trick here is to count the number of elements in the array that you get when you split the text.<\/p>\n<p>The below code would show a <a href=\"https:\/\/trumpexcel.com\/vba-msgbox\/\" target=\"_blank\" rel=\"noopener noreferrer\">message box<\/a> with the word count:<\/p>\n<pre>Sub WordCount()\nDim TextStrng As String\nDim WordCount As Integer\nDim Result() As String\nTextStrng = \"The Quick Brown Fox Jumps Over The Lazy Dog\"\nResult = Split(TextStrng)\nWordCount = UBound(Result()) + 1\nMsgBox \"The Word Count is \" &amp; WordCount\nEnd Sub<\/pre>\n<p><img decoding=\"async\" class=\"size-full wp-image-26019 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Getting-the-Word-Count-message.png\" alt=\"VBA Split Function - Getting the Word Count message\" width=\"184\" height=\"186\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Getting-the-Word-Count-message.png 184w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Getting-the-Word-Count-message-50x50.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Getting-the-Word-Count-message-99x100.png 99w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Getting-the-Word-Count-message-148x150.png 148w\" sizes=\"(max-width: 184px) 100vw, 184px\" \/><\/p>\n<p>In this case, the UBound function tells us the upper bound of the array (i.e., the maximum number of elements the array has). Since the base of the array is 0, 1 is added to get the total word count.<\/p>\n<p>You can use a similar code to create a custom function in VBA that will take the text as input and return the word count.<\/p>\n<p>The below code will create this function:<\/p>\n<pre>Function WordCount(CellRef As Range)\nDim TextStrng As String\nDim Result() As String\nResult = Split(WorksheetFunction.Trim(CellRef.Text), \" \")\nWordCount = UBound(Result()) + 1\nEnd Function<\/pre>\n<p>Once created, you can use the WordCount function just like any other regular function.<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"size-full wp-image-26021 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-word-count-formula.png\" alt=\"VBA Split Function - word count formula\" width=\"713\" height=\"138\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-word-count-formula.png 713w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-word-count-formula-600x116.png 600w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-word-count-formula-50x10.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-word-count-formula-250x48.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-word-count-formula-400x77.png 400w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-word-count-formula-350x68.png 350w\" sizes=\"(max-width: 713px) 100vw, 713px\" \/><\/p>\n<p>This function also handles leading, trailing and double spaces in between words. This has been made possible by using the <a href=\"https:\/\/trumpexcel.com\/excel-trim-function\/\" target=\"_blank\" rel=\"noopener\">TRIM function<\/a> in the VBA code.<\/p>\n<p>In case you want to learn more about how this formula works to count the number of words in a sentence or want to learn about a non-VBA formula way to get the word count, <a href=\"https:\/\/trumpexcel.com\/word-count-in-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\">check out this tutorial<\/a>.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example-3-%E2%80%93-Using-a-Delimiter-Other-than-Space-Character\"><\/span>Example 3 &#8211; Using a Delimiter Other than Space Character<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>In the previous two examples, we have only used one argument in the SPLIT function, and the rest were the default arguments.<\/p>\n<p>When you use some other delimiter, you need to specify that in the SPLIT formula.<\/p>\n<p>In the below code, the SPLIT function returns an array based on a comma as the delimiter, and then shows a message with each word in a separate line.<\/p>\n<pre>Sub CommaSeparator()\nDim TextStrng As String\nDim Result() As String\nDim DisplayText As String\nTextStrng = \"The,Quick,Brown,Fox,Jump,Over,The,Lazy,Dog\"\nResult = Split(TextStrng, \",\")\nFor i = LBound(Result()) To UBound(Result())\nDisplayText = DisplayText &amp; Result(i) &amp; vbNewLine\nNext i\nMsgBox DisplayText\nEnd Sub<\/pre>\n<p><img decoding=\"async\" class=\"size-full wp-image-26023 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-comma-delimiter-separate-line.png\" alt=\"VBA Split Function - comma delimiter separate line\" width=\"160\" height=\"338\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-comma-delimiter-separate-line.png 160w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-comma-delimiter-separate-line-142x300.png 142w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-comma-delimiter-separate-line-24x50.png 24w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-comma-delimiter-separate-line-118x250.png 118w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-comma-delimiter-separate-line-47x100.png 47w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-comma-delimiter-separate-line-95x200.png 95w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-comma-delimiter-separate-line-71x150.png 71w\" sizes=\"(max-width: 160px) 100vw, 160px\" \/><\/p>\n<p>In the above code, I have used the <a href=\"https:\/\/trumpexcel.com\/vba-loops\/\" target=\"_blank\" rel=\"noopener noreferrer\">For Next loop<\/a> to go through each element of the &#8216;Result&#8217; array assign it to the &#8216;DisplayText&#8217; variable.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example-4-%E2%80%93-Divide-an-Address-into-three-parts\"><\/span>Example 4 &#8211; Divide an Address into three parts<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>With the SPLIT function, you can specify how many numbers of splits you want to get. For example, if I don&#8217;t specify anything, every instance of the delimiter would be used to split the string.<\/p>\n<p>But if I specify 3 as the limit, then the string will be split into three parts only.<\/p>\n<p>For example, if I have the following address:<\/p>\n<pre><em>2703 Winifred Way, Indianapolis, Indiana, 46204<\/em><\/pre>\n<p>I can use the Split function in VBA to divide this address into three parts.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-26013 aligncenter\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Specifying-number-of-elements.png\" alt=\"VBA Split Function - Specifying number of elements\" width=\"698\" height=\"86\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Specifying-number-of-elements.png 698w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Specifying-number-of-elements-600x74.png 600w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Specifying-number-of-elements-50x6.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Specifying-number-of-elements-250x31.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Specifying-number-of-elements-400x49.png 400w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-Specifying-number-of-elements-350x43.png 350w\" sizes=\"(max-width: 698px) 100vw, 698px\" \/><\/p>\n<p>It splits the first two based on the comma delimiter and remaining part becomes the third element of the array.<\/p>\n<p>The below code would show the address in three different lines in a message box:<\/p>\n<pre>Sub CommaSeparator()\nDim TextStrng As String\nDim Result() As String\nDim DisplayText As String\nTextStrng = \"2703 Winifred Way, Indianapolis, Indiana, 46204\"\nResult = Split(TextStrng, \",\", 3)\nFor i = LBound(Result()) To UBound(Result())\nDisplayText = DisplayText &amp; Result(i) &amp; vbNewLine\nNext i\nMsgBox DisplayText\nEnd Sub<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-26024 size-full\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines.png\" alt=\"Resulting Address in separate lines in a message box\" width=\"176\" height=\"223\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines.png 176w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-39x50.png 39w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-79x100.png 79w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-158x200.png 158w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-118x150.png 118w\" sizes=\"(max-width: 176px) 100vw, 176px\" \/><\/p>\n<p>One of the practical uses of this could be when you want to divide a single line address into the format shown in the message box. Then you can create a custom function that returns the address divided into three parts (with each part in a new line).<\/p>\n<p>The following code would do this:<\/p>\n<pre>Function ThreePartAddress(cellRef As Range)\nDim TextStrng As String\nDim Result() As String\nDim DisplayText As String\nResult = Split(cellRef, \",\", 3)\nFor i = LBound(Result()) To UBound(Result())\nDisplayText = DisplayText &amp; Trim(Result(i)) &amp; vbNewLine\nNext i\nThreePartAddress = Mid(DisplayText, 1, Len(DisplayText) - 1)\nEnd Function<\/pre>\n<p>Once you have this code in the module, you can use the function (ThreePartAddress) in the workbook just like any other Excel function.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"imgborder aligncenter wp-image-26025 size-full\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-formula.png\" alt=\"Split Function in VBA- address in separate lines formula\" width=\"755\" height=\"371\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-formula.png 755w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-formula-600x295.png 600w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-formula-50x25.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-formula-250x123.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-formula-204x100.png 204w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-formula-400x197.png 400w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-in-separate-lines-formula-305x150.png 305w\" sizes=\"(max-width: 755px) 100vw, 755px\" \/><\/p>\n<p>This function takes one argument &#8211; the cell reference that has the address.<\/p>\n<p>Note that for the resulting address to appear in three different lines, you need to apply the wrap text format to the cells (it&#8217;s in the Home tab in the Alignment group). If the &#8216;Wrap Text&#8217; format is not enabled, you&#8217;ll see the entire address as one single line.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Example-5-%E2%80%93-Get-the-City-Name-from-the-Address\"><\/span>Example 5 &#8211; Get the City Name from the Address<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>With Split function in VBA, you can specify what part of the resulting array you want to use.<\/p>\n<p>For example, suppose I am splitting the following address based on\u00a0the comma as the\u00a0delimiter:<\/p>\n<pre>2703 Winifred Way, Indianapolis, Indiana, 46204<\/pre>\n<p>The resulting array would look something as shown below:<\/p>\n<pre>{\"2703 Winifred Way\", \"Indianapolis\", \"Indiana\", \"46204\"}<\/pre>\n<p>Since this is an array, I can choose to display or return a specific part of this array.<\/p>\n<p>Below is a code for a custom function, where you can specify a number and it will return that element from the array. For example, if I want the state name, I can specify 3 (as it&#8217;s the third element in the array).<\/p>\n<pre>Function ReturnNthElement(CellRef As Range, ElementNumber As Integer)\nDim Result() As String\nResult = Split(CellRef, \",\")\nReturnNthElement = Result(ElementNumber - 1)\nEnd Function<\/pre>\n<p>The above function takes two arguments, the cell reference that has the address and the element number you want to return. The Split function splits the address elements and assigns it to the Result variable.<\/p>\n<p>Then it returns the element number that you specified as the second argument. Note that since the base is 0, ElementNumber-1 is used to return the correct part of the address.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"imgborder aligncenter wp-image-26026 size-full\" src=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-element-by-number.png\" alt=\"VBA Split Function - address element by number\" width=\"663\" height=\"230\" srcset=\"https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-element-by-number.png 663w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-element-by-number-600x208.png 600w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-element-by-number-50x17.png 50w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-element-by-number-250x87.png 250w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-element-by-number-400x139.png 400w, https:\/\/trumpexcel.com\/wp-content\/uploads\/2018\/05\/VBA-Split-Function-address-element-by-number-350x121.png 350w\" sizes=\"(max-width: 663px) 100vw, 663px\" \/><\/p>\n<div class=\"cbyellow\">This custom formula is best suited when you have a consistent format in all the address &#8211; i.e., the city is always mentioned after the two commas. If the data is not consistent, you&#8217;ll not get the desired result.<\/div>\n<p>In case you want the city name, you can use 2 as the second argument. In case you use a number that is higher than the total number of elements, it would return the #VALUE! error.<\/p>\n<p>You can further simplify the code as shown below:<\/p>\n<pre>Function ReturnNthElement(CellRef As Range, ElementNumber As Integer)\nReturnNthElement = Split(CellRef, \",\")(ElementNumber - 1)\nEnd Function<\/pre>\n<p>In the above code, instead of using the Result variable, it only returns the specified element number.<\/p>\n<p>So if you have Split(&#8220;Good Morning&#8221;)(0), it would only return the first element, which is &#8220;Good&#8221;.<\/p>\n<p>Similarly, in the above code, it only returns the specified element number.<\/p>\n<p><strong>You May Also Like the Following Excel Tutorials:<\/strong><\/p>\n<ul>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-vba-instr\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel VBA InStr Function &#8211; Explained with Examples.<\/a><\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/sort-data-vba\/\" target=\"_blank\" rel=\"noopener noreferrer\">How to Sort Data in Excel using VBA (A Step-by-Step Guide)<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/excel-text-to-columns-examples\/\" target=\"_blank\" rel=\"noopener noreferrer\">7 Amazing Things Excel Text to Columns Can Do For You<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/word-count-in-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\">How to Get the Word Count in Excel<\/a>.<\/li>\n<li><a href=\"https:\/\/trumpexcel.com\/vba-trim\/\" target=\"_blank\" rel=\"noopener noreferrer\">VBA TRIM Function<\/a>.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>When working with VBA in Excel, you may have a need to split a string into different parts based on a delimiter. For example, if you have an address, you can use the VBA Split function to get different parts of the address that are separated by a comma (which would be the delimiter in &#8230; <\/p>\n<p class=\"read-more-container\"><a title=\"Excel VBA Split Function &#8211; Explained with Examples\" class=\"read-more button\" href=\"https:\/\/trumpexcel.com\/vba-split-function\/#more-25998\" aria-label=\"Read more about Excel VBA Split Function &#8211; Explained with Examples\">Read more<\/a><\/p>\n","protected":false},"author":80,"featured_media":26025,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[154,9],"tags":[118,145,139],"class_list":["post-25998","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-vba-functions","category-vba-tips","tag-excel-vba","tag-vba-examples","tag-vba-function"],"_links":{"self":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts\/25998","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/users\/80"}],"replies":[{"embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/comments?post=25998"}],"version-history":[{"count":0,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/posts\/25998\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/media\/26025"}],"wp:attachment":[{"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/media?parent=25998"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/categories?post=25998"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/trumpexcel.com\/wp-json\/wp\/v2\/tags?post=25998"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}