How to Use VBA SPLIT Function in Excel (5 Examples)

Excel provides several VBA functions that can be used while writing a macro or to define your own function to perform your desired tasks. In this article, we are going to show you a VBA function called SPLIT.

Overview - VBA SPLIT Function

Practice Workbook

You are welcome to download the practice workbook from the link below.

VBA SPLIT Function

1. Basics of VBA SPLIT

Summary

The VBA SPLIT function in Excel splits the string into several substrings and returns a one-dimensional array of substrings.

Syntax

Split( expression, [delimiter], [limit], [compare] )

Arguments

Argument Required/Optional Description
expression Required The string to split based on the delimiter
delimiter Optional The character that divides the string into parts
limit Optional Number of substrings to be returned
compare Optional Numeric value to indicate what type of comparison to use when evaluating substrings

0  – Binary Comparison

1- Textual Comparison

2. Uses of VBA SPLIT

I. Split Words from String

As per the description of the function, you have understood that the SPLIT function will split the string into substrings.

Example String - VBA SPLIT Function

To split the string we will use the code written below.

Sub WordSeparator()

Dim TextStrng As String

Dim Result() As String

Dim DisplayText As String

TextStrng = "We are learning VBA SPLIT"

Result = Split(TextStrng)

For i = LBound(Result()) To UBound(Result())

     DisplayText = DisplayText & Result(i) & vbNewLine

Next i

MsgBox DisplayText

End Sub

Write the code at the Microsoft Visual Basic for Applications (You will find it under the Developer tab or can use the ALT + L + V shortcut to open the window)

Word separator msgbox - VBA SPLIT Function

Here we set the name of the macro as WordSeperator. Then declared three variables using the Dim keyword. All the variables are string types.

We have set the string We are learning VBA SPLIT in one of the variables (TextString). Then applied the SPLIT function to the string using that variable and stored it in variable Result. 

Here we have used a For loop where LBound and UBound set the range. LBound is the least length a string can have (0) and the UBound returns the length of the string.

This loop helps us to store the substring into the DisplayText variable. Then added a MsgBox to return the output in a message box format.

Run the code. You will see the output in a message box.

Word Separated in Msgbox - VBA SPLIT Function

Since we didn’t mention any delimiter, it assumed each substring up to a space.

If you want the output in cells rather than a message box then use the cell number instead of MsgBox.

Word separator in cell code - VBA SPLIT Function

Here we have added the value into the Cells, where i+3 and 2 are the row and column numbers respectively. Now Run the code

Word separator in cell result - VBA SPLIT Function

The output will be on the Excel cells.

II. Split Words Separated with Delimiters

If we don’t use any specific delimiter then the SPLIT function will split assuming space as the delimiter.

A delimiter can be a comma, semicolon, or any separating character.

Here we are using a comma as the delimiter.

Comma separated text - VBA SPLIT Function

Our code will be

Sub CommaSeparator()

Dim TextStrng As String

Dim Result() As String

Dim DisplayText As String

TextStrng = "Apples, Bananas,Oranges,Mangoes"

Result = Split(TextStrng, ",")

For i = LBound(Result()) To UBound(Result())

    Cells(i+3, 2).Value = Result(i)

Next i

MsgBox DisplayText

End Sub

 

Comma separator code - VBA SPLIT Function

You can see the only change of our code is we have used the delimiter field in the SPLIT function and inserted comma (,) there. This denotes that the string will be split into the comma.

Run the code and you will see the output.

Comma separator result- VBA SPLIT Function


III. Divide into Multiple Parts

We can divide the strings into multiple parts, it seems a bit similar to the split string into substrings. This time we are going to use the SPLIT in our own defined function.

Here our example dataset consists of few addresses. Each address contains three parts.

Address in Parts data - VBA SPLIT Function

We are set to divide these three parts into one each. Our code will be,

Function ThreePartAddress(cellRef As Range)

Dim TextStrng As String

Dim Result() As String

Dim DisplayText As String

Result = Split(cellRef, ",", 3)

For i = LBound(Result()) To UBound(Result())

    DisplayText = DisplayText & Trim(Result(i)) & vbNewLine

Next i

ThreePartAddress = Mid(DisplayText, 1, Len(DisplayText) - 1)

End Function

Within our code, we have inserted the limit field in the SPLIT function. Since we want to divide into three parts so we have used 3 at the limit parameter.

Address in three parts - VBA SPLIT Function

We have used VBA LEN and MID functions that return the length of the string and extract the middle value from the text.

Now save the code and use the function.

Use the function for any of the rows. Let’s for the first row

ThreePartAdress function - VBA SPLIT Function

We will find the address separated into three parts (see the image below).

ThreePartAddress output - VBA SPLIT Function

You can use the function as many times as you want.

ThreePartAddress autofill - VBA SPLIT Function

IV. Count Words using SPLIT

The SPLIT function will be helpful to count the words from a string. To show you examples, we have brought a dataset of a few random sentences.

Count words dataset - VBA SPLIT Function

We will find the total word of these sentences. We are going to create our own function using SPLIT.

The code will be

Function WordCount(CellRef As Range)

Dim TextStrng As String

Dim Result() As String

Result = Split(WorksheetFunction.Trim(CellRef.Text), " ")

WordCount = UBound(Result()) + 1

End Function 

 

WordCount function code

We have trimmed the text using VBA TRIM function and split it by space (though we can skip this as by default split by space).

In the WordCount variable, we have added the UBound of the string with 1.

Save the code and write the function for the sentence.

WordCount function

We have found the total number of words present in the sentence.

WordCount result

You can write the function for the other sentences as well.

WordCount Autofill

V. Find any Specific Word from a String

We can find and retrieve any specific word from a string.

We are using the address dataset for this example.

Return Nth Value dataset - VBA SPLIT Function

Now, we are going to fetch the name of the city from the address. We can find any nth number of values from the string, for that we need to set the number of that particular element from the string.

And our code will be

Function ReturnNthValue(CellRef As Range, ValueNumber As Integer)

Dim Result() As String

Result = Split(CellRef, ",")

ReturnNthValue = Result(ValueNumber - 1)

End Function

 

Return Nth Value function code

Here our function takes two parameters; the string and the value number. The SPLIT function splits the string by a delimiter comma. It returns the substrings in an array.

The array index is one less than the total element. You can see our stored addresses have 4 parts separated by a comma. But the array index number will be one less than that (3).  That’s why we have subtracted 1 from the provided value number.

Save the code and use the function.

Return Nth Value function use

 

We have provided the string and 3 (city at the 3rd position) and found the city name in return. A similar result will be for the rest of the values.

Return Nth Value function autofill


Similar Readings


3. Things to Remember

SPLIT stores the result as an array. From our first example, we have not set any output methods and so just write the code like this:

Sub WordSeparator()

Dim TextStrng As String

Dim Result() As String

Dim DisplayText As String

TextStrng = "We are learning VBA SPLIT"

Result = Split(TextStrng)

End Sub

 

Word Separator code without output

Running this code you may not see any output but the string would be divided into substrings and be stored within the array.

Conclusion

That’s all for today. We have tried showing how you can use the VBA SPLIT function. You can use the function to split the string into substrings as well as count the number of words from the string. Hope you will find this helpful.

Feel free to comment if anything seems difficult to understand. Let us know any of your SPLIT function-related scenarios where you have stuck, we are ready to help.


Related Articles

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo