VBA Split String into Array

A string is a collection of characters joined together. When these characters are divided and stored in a variable, that variable becomes an array for these characters. The method we use to split a string into an array is by using the SPLIT function in VBA, which splits the string into a one-dimensional string.

Like worksheets in VBA, too, we have functions to deal with String or Text values. We are familiar with the string operations like extracting the first name, last name, middle name, etc. But how about the idea of splitting string values into arrays in VBA? Yes, you heard it correctly we can split string sentences into an array using VBA coding. This special article will show you how to split the string into an array in Excel VBA.

Let me clarify this first, "String into Array" is nothing but "different parts of the sentence or string will split into multiple parts." So, for example, if the sentence is "Bangalore is the capital city of Karnataka," each word is a different array.

So, how to split this sentence into an array is the topic of this article.

To convert the split string into an array in VBA, we have a function called "SPLIT." This VBA function splits supplied string values into different parts based on the delimiter provided.

For example, if the sentence is "Bangalore is the capital city of Karnataka," space is the delimiter between each word.

Below is the syntax of the SPLIT function.

• Value or Expression: This is the string or text value we try to convert to the array by segregating each part of the string.
• : This is nothing but the common things which separate each word in the string. In our sentence, "Bangalore is the capital city of Karnataka," each word is separated by a space character, so our delimiter is space here.
• : Limit is nothing but how many parts we want as a result. For example, in the sentence "Bangalore is the capital city of Karnataka," we have seven parts. If we need only three parts, then we will get the first part as "Bangalore," the second part as "is," and the third part as the rest of the sentence, i.e., "the capital city of Karnataka."
• : One does not use this99% of the time, so let us not touch this.

Example #1

Now, let us see practical examples.

Step 1: Define the VBA variable to hold the string value.

Code:

Sub String_To_Array()

  Dim StringValue As String

End Sub

Step 2: For this variable, assign the string "Bangalore is the capital city of Karnataka."

Code:

Sub String_To_Array()

 Dim StringValue As String
 StringValue = "Bangalore is the capital city of Karnatka"

End Sub

Step 3: Next, define one more variable which can hold each part of the above string value. We need to remember this because the sentence has more than one word, so we need to define the variable as “Array” to hold more than one value.

In this case, we have 7 words in the string so define the array as follows.

Code:

Sub String_To_Array()

 Dim StringValue As String
 StringValue = "Bangalore is the capital city of Karnatka"

 Dim SingleValue() As String

End Sub

Now, for this array variable, we will use the SPLIT function to split the string into an array in Excel VBA.

Code:

Sub String_To_Array()

 Dim StringValue As String
 StringValue = "Bangalore is the capital city of Karnataka"

 Dim SingleValue() As String
 SingleValue = Split(StringValue, " ")

End Sub

The expression is our string value, i.e., the variable already holds the string value, so enter the variable name only.

The delimiter in this string is a space character so supply the same.

Code:

Sub String_To_Array()

 Dim StringValue As String
 StringValue = "Bangalore is the capital city of Karnataka"

 Dim SingleValue() As String
 SingleValue = Split(StringValue, " ")

End Sub

As of now, leave other parts of the SPLIT function.

The SPLIT function splits the string value into 7 pieces, each word segregated at the expense of space character. Since we have declared the variable "SingleValue" as an array, we can assign all 7 values to this variable.

We can write the code as follows.

Code:

Sub String_To_Array()

 Dim StringValue As String
 StringValue = "Bangalore is the capital city of Karnataka"

 Dim SingleValue() As String
 SingleValue = Split(StringValue, " ")

 MsgBox SingleValue(0)

End Sub

Run the code and see what we get in the message box.

Now, we can see the first word, "Bangalore." To show other words, we can write the code as follows.

Code:

Sub String_To_Array()

 Dim StringValue As String
 StringValue = "Bangalore is the capital city of Karnataka"

 Dim SingleValue() As String
 SingleValue = Split(StringValue, " ")

 MsgBox SingleValue(0) & vbNewLine & SingleValue(1) & vbNewLine
 & SingleValue(2) & vbNewLine & SingleValue(3) & _vbNewLine &
 SingleValue(4) & vbNewLine & SingleValue(5) & vbNewLine & SingleValue(6)

End Sub

Now, run the code and see what we get in the message box.

Each and every word has been split into arrays.

Example #2

Now, imagine a situation of storing these values in cells, i.e., each word in a separate cell. For this, we need to include the FOR NEXT loop in VBA.

The below code will insert each word into separate cells.

Sub String_To_Array1()

 Dim StringValue As String
 StringValue = "Bangalore is the capital city of Karnataka"

 Dim SingleValue() As String
 SingleValue = Split(StringValue, " ")

 Dim k As Integer

 For k = 1 To 7
  Cells(1, k).Value = SingleValue(k - 1)
 Next k

End Sub

It will insert each word, as shown in the below image.

• One may use arrays and loops to make the code dynamic.
• The SPLIT function requires a common delimiter, which separates each word in the sentence.
• Array length starts from zero, not from 1.