Table of Contents
To split a string into an array using VBA, you can use the built-in function “Split”. This function takes two arguments – the string to be split and the delimiter that will determine where the string will be split. The output of this function is an array with the individual elements of the string. For example, if we have a string Hello World” and use a space (” “) as the delimiter, the resulting array will have two elements – Hello” and “World”. This function can be useful for manipulating strings and extracting specific information. An example of using the “Split” function in VBA is shown below:
Dim myString As String
myString = “I love programming”
Dim myArray() As String
myArray() = Split(myString, ” “)
‘ myArray now contains {“I”, “love”, “programming”}
VBA: Split String into Array (With Example)
You can use the following basic syntax to split a string into an array using VBA:
Sub SplitString()
Dim SingleValue() As String
Dim i As Integer
Dim j As Integer
For i = 2 To 7
SingleValue = Split(Range("A" & i), " ")
For j = 1 To 2
Cells(i, j + 1).Value = SingleValue(j - 1)
Next j
Next i
End Sub
This particular example splits each string in the range A2:A7 based on spaces in each string and then assigns the resulting elements of each string into cell ranges B2:B7 and C2:C7.
The following example shows how to use this syntax in practice.
Example: Using VBA to Split String into Array
Suppose we have the following list of strings in Excel:
Suppose we would like to split each string based on spaces and then assign the resulting elements of each string to new cells.
We can create the following macro to do so:
Sub SplitString()
Dim SplitValues() As String
Dim i As Integer
Dim j As Integer
For i = 2 To 7
SplitValues = Split(Range("A" & i), " ")
For j = 1 To 2
Cells(i, j + 1).Value = SplitValues(j - 1)
Next j
Next i
End SubWhen we run this macro, we receive the following output:
Notice that columns B and C contain the first and last names of each full name in column A.
Also note that you can use the VBA Split function to split an array based on a different delimiter.
For example, suppose we have the following list of emails:
Suppose we would like to split each email into an array based on where the @ symbol occurs.
Sub SplitString()
Dim SplitValues() As String
Dim i As Integer
Dim j As Integer
For i = 2 To 7
SplitValues = Split(Range("A" & i), "@")
For j = 1 To 2
Cells(i, j + 1).Value = SplitValues(j - 1)
Next j
Next i
End SubWhen we run this macro, we receive the following output:
The Split function split each email in column A based on the @ symbol and outputted the resulting elements in columns B and C.
Note: You can find the complete documentation for the VBA Split function .
The following tutorials explain how to perform other common tasks using VBA:
Cite this article
stats writer (2024). How can I split a string into an array using VBA? Can you provide an example?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-split-a-string-into-an-array-using-vba-can-you-provide-an-example/
stats writer. "How can I split a string into an array using VBA? Can you provide an example?." PSYCHOLOGICAL SCALES, 24 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-split-a-string-into-an-array-using-vba-can-you-provide-an-example/.
stats writer. "How can I split a string into an array using VBA? Can you provide an example?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-split-a-string-into-an-array-using-vba-can-you-provide-an-example/.
stats writer (2024) 'How can I split a string into an array using VBA? Can you provide an example?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-split-a-string-into-an-array-using-vba-can-you-provide-an-example/.
[1] stats writer, "How can I split a string into an array using VBA? Can you provide an example?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I split a string into an array using VBA? Can you provide an example?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.