VBA Split String into Array

Excel VBA Split String into Array

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

Like worksheets in VBA too, we have functions to deal with String or Text values. We are very much familiar with the string operations like extracting the firs name, last name, middle name, etc. But how about the idea of splitting string value into arrays in VBA? Yes, you heard it correct we can split string sentence into array by using VBA codingUsing VBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more, and in this special article, we will show you how to split the string into an array in Excel VBA.

VBA-Split-String-into-Array

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Split String into Array (wallstreetmojo.com)

What is Split String into an Array?

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

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

How to Convert Split String into an Array in Excel VBA?

To convert the split string into an array in VBA we have a function called “SPLIT.” This is a VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more that performs the task of splitting supplied string value 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.

Split Function
  • Value or Expression: This is the string or text value that we are trying to convert to the array by segregating each part of the string.
  • [Delimiter]: 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 separated by space character, so our delimiter is space here.
  • [Limit]: Limit is nothing but how many parts we want as a result. For an 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 third part as rest of the sentence i.e. “the capital city of Karnataka.”
  • [Compare]: This is not used 99% of the time, so let’s not touch this at this point in time.
You can download this VBA Split String into Array Excel Template here – VBA Split String into Array Excel Template

Example #1

Ok, now let’s see practical examples.

Step 1: Define the VBA variableDefine The VBA VariableVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.read more to hold the string value.

Code:

Sub String_To_Array()

  Dim StringValue As String

End Sub
VBA Split String into Array - Example 1

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
VBA Split String into Array - Example 1-1

Step 3: Next, define one more variable which can hold each part of the above string value. On this we need to keep in mind here is since the sentence has more than one word, 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
VBA Split String into Array - Example 1-2

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
VBA Split String into Array - Example 1-3

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

VBA Split String into Array - Example 1-4

The delimiter in this string is 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.

SPLIT function split the string value into 7 pieces, each word segregated at the expense of space character. Since we have declared the variable “SingleValue” as array, we can assign all the 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.

Example 1-5

As of now, we can see the first word i.e. “Bangalore,” to show further 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.

Example 1-6

Each and every word has been split into arrays.

Example #2

Now imagine a situation of storing these values to cells i.e., each word into a separate cell. For this, we need to include the FOR NEXT loop in VBAFOR NEXT Loop In VBAAll programming languages make use of the VBA For Next loop. After the FOR statement, there is a criterion in this loop, and the code loops until the criteria are reached. read more.

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

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

Example 2

Things to Remember

  • Array and loops are used together to make the code dynamic.
  • The SPLIT function requires common delimiter, which separates each word in the sentence.
  • Array length starts from zero, not from 1.

This has been a guide to VBA Split String into Array. Here we discuss how to convert a split string into an array in excel VBA along with practical examples. You can learn more about VBA functions from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>