Excel VBA Split String Into Array - WallStreetMojo

All Courses LoginAll BlogsVBA ResourcesVBA Split String into ArrayVBA Split String into Array

Publication Date :

09 Dec, 2019

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Share

GET: WSM ALL COURSES ACCESSDownload FREE VBA Split String into Array Template and Follow Along!VBA Split String into Array Excel Template.xlsm

Table Of Contents

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, 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.

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 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.

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 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.

Things to Remember

• 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.

FINANCIAL MODELING 2-DAY BOOTCAMP @70% OFF – YEAR END SALE

Master Financial Modeling in Just 2 Days | Build and Forecast IS, BS & CF from Scratch | Perfect for Graduates and Professionals Looking to Upskill.

Join WallStreetMojo YouTube

INVESTMENT BANKING MASTERY PROGRAM @70% OFF – YEAR END SALE

Experience the Exact Training Used by Top Investment Banks | Learn FM, DCF, LBO, M&A, Accounting & More | Unlock Wall Street-Level Skills.

Join WallStreetMojo Instagram

CHATGPT & AI FOR EXCEL COURSE @70% OFF – YEAR END SALE

Boost Productivity 10X with AI-Powered Excel | Automate Reports, Eliminate Errors & Advance Your Analytics Skills | Transform Your Workflow.

Join WallStreetMojo LinkedIn

EXCEL ALL-IN-ONE BUNDLE COURSE @70% OFF – YEAR END SALE

Master Excel, VBA & Power BI Like a Pro | 70+ Hours of Expert-Led Training | Learn Real-World Applications & Earn Your Certification.

Join WallStreetMojo Facebook

Join WallStreetMojo X