Excel VBA Split: How to Break a String into Manageable Chunks

You can use Visual Basic for Applications (VBA), Microsoft’s programming language for the MS Office suite software, to greatly enhance the capability of Excel. You can write macros, or programs that can help you repeat previously performed tasks, using VBA. Macros can really improve your productivity and cut down on the time you spend doing things manually. This VBA tutorial gives some good examples of how you can use VBA to help automate things.

VBA is prebuilt into Microsoft Excel (and other office suite applications). To access VBA, you need to enable the developer tab first, if you haven’t already. Click on a spot on your ribbon (at the top) and choose the Customize the Ribbon option. By default, the Developer tab is unchecked. Enable it and click on Ok. A new tab should appear next to the View tab on top of your screen. This is the Developer tab, through which you can access VBA and Macros in general. The Developer tab lets you add VBA objects to your spreadsheet (you can check out this course to learn more about using VBA and Macros with Excel).

Like other programming languages, VBA lets you play around with strings and perform various operations on them. The split function is the function that lets you work with lengthy strings in Excel.If you’re new to VBA, or programming languages in general, and don’t know what strings are, we recommend you sign up for our Excel VBA beginner course for an introduction to the language.

The Excel VBA Split

The Excel VBA Split function was introduced in the year 2000, in response to a growing need to have a function that could handle large strings (for example, “John Harry David Smith” or “welcome to this tutorial”). The split function is used to split, or break, a string into manageable chunks.

If you’re familiar with Excel spreadsheets (if not, you can take a basic Excel course here), you know that sometimes parts of a long name get hidden – they can’t fit in the confines of a single cell. With the split function, what you can do is split the string into smaller parts and get them to occupy separate cells. For example, if you had a name “John Harry David Smith” and it was occupying cell A1, you can use the split function to separate the name and get it to occupy cells A1, B1, C1 and D1. Sounds useful, doesn’t it? Of course, this is just one of the uses of a split function.

VBA Split Syntax

What the split function does is split a string into an array and then returning that array. This array is one-dimensional. So how do you write a split function? The syntax for the split function is:

Split (string, delimiter, limit, compare)

Here, the string is the text you want to split. The “delimiter” will decide how the text will be split into parts. Usually, the space character “ “ occupies this part of the code. If you don’t define a delimiter, a space character “ “ will be used by default. The limit defines the number of the strings is allowed to divide into. By default, the limit is linked to the delimiter (default value of -1). This means that the delimiter part of the code will decide the number of parts the string gets divided into. The compare method lets you specify the method of comparison: binary (0) or text (1). You don’t have to specify the compare parameter either. By default, it will revert to binary (0).

Example 1

Let’s take a couple of examples to help you understand how the string function works. In the first example, let’s split the string “welcome to this tutorial” and print the words of the string separately (on a new line). The code for that is as follows:

Sub Example()
Dim text As String
Dim message As Variant
Dim a As Long
text = "welcome to this tutorial"
message = Split(text, " ")
For a = 0 To UBound(message)
Debug.Printmessage(a)
Next a
End Sub

Try running this code and see what happens. You will get the message “welcome to this tutorial” printed on 4 different lines.

So how does the code work exactly? First, we initialized text as a string variable, message as a variant variable and a as long variable. A variant variable is a necessity if you are working with the split function. It allows the string text to be treated as split-able. We followed the syntax and used the default delimiter “ “. Finally, we used a for loop to get the array printed on separate lines. 0 defines the starting position of the array and UBound defines the maximum (ending) position of the array.

Example 2

Now let’s write a program to separate a big name (John Harry David Smith) written on a single cell in Excel and get it printed on 4 different cells. Let’s write the name on cell A1 as in the image:

The code for  that is as follows:

Sub example()
Dim text As String
Dim a As Integer
Dim name As Variant
text = ActiveCell.Value
name = Split(text, " ")
For a = 0 To UBound(name)
Cells(1, a + 1).Value = name(a)
Next a
End Sub

The code in this example is similar to the code in the previous example. The ActiveCell.Value tells the computer to look for the active cell (A1) and get the string value written there. The split function is used exactly like we have in the previous example. The Cells(1, a + 1).Value part of the code accesses the first row (1) of the spreadsheet. The loop is used to run through the parts of the array and get the string separated and printed into different cells. The final result should look like this, once your run your code:

You can see how useful the split function is. You can also use the split function to omit unnecessary words or symbols (like the “.” character) from your string.

We recommend that you practice with the code we’ve given above to find out how to do that. Try to change the text entered and change the value of the delimiter, limit and compare parts of the split function syntax. Alternatively, you can sign up for our advanced Excel VBA course, where we teach you how to do that, and about using VBA efficiently in general.