Arrays and the Split function

In the previous section on strings, we mentioned that there is an inbuilt function called Split . We said that this comes in handy if you want to split a name that has more than two part. Our previous code only worked for people who had one first name and a surname. But what if the name you come across in cell A1 is something like David Lloyd George? Well, that's where the Split function can help.

The Split function looks like this:

Split(text_here, separator)

Between the round brackets of Split you need two things. The first is the string you want to break up into separate pieces. After a comma, you then need something called the separator . This is whatever character is between each piece of text you want to split. It could be a blank space, a comma, a dash, just about anything.

(NOTE: there are also two optional parameters you can add to Split, a limit and a compare method. The limit is an integer and is used to restrict the number of pieces that Split produces. The compare methods are CompareMethod.Binary and CompareMethod.Text .)

As an example of Split , try out the following code:

The first two lines set up a String and an Integer variable, txt and i . The third line sets up a variable of type Variant . A Variant type is needed if you're going to be using Split. Any other variable type won't work.

The fourth line just gets the value of the ActiveCell on the spreadsheet and places its contents into the txt variable. Next comes the Split line:

FullName = Split(txt, " ")

To the left of the equal sign we have our FullName Variant variable. The pieces of the string will be split and stored here, turning FullName into an array.

To the right of the equal sign we have the Split function:

​ Split(txt, " ")

The first item between the round brackets of Split is the text we want to split. The text for us is held in the variable called txt . But you don't have to store your text in variable. You could just have direct text surrounded by double quotes:

Split("David Lloyd George", " ")

The second item between the round brackets of Split is the separator. We want to separate each part of the string wherever spaces are found. We've typed two double quotes. Between the double quotes, we tapped the space bar on the keyboard once. The Split function will then search for 1 space between each word of the text, and break it into chunks where this space is found. Each chunk will be one item in the array.

To get at each piece of the new FullName array, we have a For loop:

For i = 0 To UBound(FullName)

Next i

The loop goes from 0 (the first position in the array) to the highest position in the array. We get this highest position by using UBound .

As the code for the loop, we have this:

Cells(1, i + 1).Value = FullName(i)

Here, we're using Cells to access the first Row, which is the hard-coded 1. To move across the columns we have i + 1. To the right of the equal sign we have our new array, FullName . To get at each position in the array we have a pair of round brackets after the array name. Between the round brackets we have the loop variable, i . This will move us through the array, as i increases by 1 each time round.

Try it out. Enter a name into cell A1 of your spreadsheet and it will look something like this:

Return to the VBA editor and run your code. Your spreadsheet should change to this:

So we've taken a name from one cell and split it over three cells.

Delete the names in all the cells of your spreadsheet. Now enter an even longer name, something like "John Allen Joe Jones". Run your code again and you'll find that all four parts end up in different cells.