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). SPLIT is an inbuilt string function in Excel VBA that you can use to split a text string based on the delimiter.
Excel VBA SPLIT Function – Syntax
Expression: 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 ‘expression’. In case this is a zero-length string (“”) SPLIT function would return an empty array. Delimiter: This is an optional argument. This is the delimiter that is used to split the ‘Expression’ 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’t specify this argument, a space character is considered the default delimiter. In case you give a zero-length string (“”), the entire ‘Expression’ string is returned by the function. Limit: 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 ‘Expression’ argument, this would be 3. If you don’t specify this argument, the default is -1, which returns all the substrings. Compare: 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: When Compare is 0: This is a Binary comparison. This means that if your delimiter is a text string (let’s say ABC), then this would be case-sensitive. ‘ABC’ would not be equal to ‘abc’. When Compare is 1: This is a Text comparison. This means that if your delimiter is a text string (let’s say ABC), then even if you have ‘abc’ in the ‘Expression’ string, it would be considered as a delimiter.
Now that we have covered the basics of the SPLIT function, let’s see a few practical examples.
Example 1 – Split the Words in a Sentence
Suppose I have the text – “The Quick Brown Fox Jumps Over The Lazy Dog”. I can use the SPLIT function to get each word of this sentence into as a separate item in an array. The below code would to this: While the code does nothing useful, it will help you understand what the Split function in VBA does. Split function splits the text string and assigns each word to the Result array. So in this case:
Result(0) stores the value “The” Result(1) stores the value “Quick” Result(2) stores the value “Brown” and so on.
In this example, we have only specified the first argument – which is the text to be split. Since no delimiter has been specified, it takes space character as the default delimiter.
Example 2 – Count the Number of Words in a Sentence
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. The below code would show a message box with the word count:
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. 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. The below code will create this function: Once created, you can use the WordCount function just like any other regular function.
This function also handles leading, trailing and double spaces in between words. This has been made possible by using the TRIM function in the VBA code. 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, check out this tutorial.
Example 3 – Using a Delimiter Other than Space Character
In the previous two examples, we have only used one argument in the SPLIT function, and the rest were the default arguments. When you use some other delimiter, you need to specify that in the SPLIT formula. 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.
In the above code, I have used the For Next loop to go through each element of the ‘Result’ array assign it to the ‘DisplayText’ variable.
Example 4 – Divide an Address into three parts
With the SPLIT function, you can specify how many numbers of splits you want to get. For example, if I don’t specify anything, every instance of the delimiter would be used to split the string. But if I specify 3 as the limit, then the string will be split into three parts only. For example, if I have the following address: I can use the Split function in VBA to divide this address into three parts.
It splits the first two based on the comma delimiter and remaining part becomes the third element of the array. The below code would show the address in three different lines in a message box:
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). The following code would do this: Once you have this code in the module, you can use the function (ThreePartAddress) in the workbook just like any other Excel function.
This function takes one argument – the cell reference that has the address. Note that for the resulting address to appear in three different lines, you need to apply the wrap text format to the cells (it’s in the Home tab in the Alignment group). If the ‘Wrap Text’ format is not enabled, you’ll see the entire address as one single line.
Example 5 – Get the City Name from the Address
With Split function in VBA, you can specify what part of the resulting array you want to use. For example, suppose I am splitting the following address based on the comma as the delimiter: The resulting array would look something as shown below: Since this is an array, I can choose to display or return a specific part of this array. 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’s the third element in the array). 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. 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.
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. You can further simplify the code as shown below: In the above code, instead of using the Result variable, it only returns the specified element number. So if you have Split(“Good Morning”)(0), it would only return the first element, which is “Good”. Similarly, in the above code, it only returns the specified element number.
Excel VBA InStr Function – Explained with Examples. How to Sort Data in Excel using VBA (A Step-by-Step Guide). 7 Amazing Things Excel Text to Columns Can Do For You. How to Get the Word Count in Excel. VBA TRIM Function.
Dim TextStrng As String Dim Result() As String Dim n As Integer ‘ n number of rows. For n = 2 To 10 ‘ 2 because of the header. TextStrng = Cells(n, 1).Value ‘ 1 : 1st column. Result = Split(TextStrng, “,”) For i = LBound(Result()) To UBound(Result()) Cells(n, 2 + i).Value = Result(i) ‘ Each value its placed in ordered cells. Next i Next n End Sub Sub testing() Dim result As Integer result = WordCount(Sheet1.Range(“d5”)) Debug.Print (result) End Sub But in an excel sheet formula: =WordCount(D5) results in 0 every time TextStrng = CellRef.Text ‘——————————————– Result = Split(TextStrng) WordCount = UBound(Result()) + 1 End Function
