How to convert string to array using VBA in Excel

For example, if you have a cell with a string of comma-separated values, you can use the Text to Columns feature to convert the string into an array of separate values. This will allow you to perform calculations and manipulate the data in ways that would not be possible if the data were stored as a single string. The same thing can be done through VBA in Excel.

In this tutorial we’ll learn how to convert the string into an array of separate values using VBA. Let’s look at the dataset above which has the comma-separated values of first name, last name and email addresses. We’ll use VBA to convert this string into separate values and write those values in separate cells. Follow along the steps below to do this.

In Microsoft Excel, you may need to convert a string into an array when you have data that is separated by a delimiter, such as a comma or a tab. This can be useful when you need to manipulate the data, such as performing calculations or sorting the data, and you want the data to be separated into individual values instead of being stored as a single string.

Step 1 – Open VBA Editor through Developer Tab or Shortcut key

– From the list of main tabs, click on the Developer Tab.
– If you can’t see the Developer tab in your list of tabs then follow this to enable developer tab in Excel.
– From the Developer tab, click on the Visual Basic action button to open VBA Editor.
– The same can be done by pressing the ALT+F11 shortcut key.

Step 2 – Write the code to convert Single String to Array

– To convert the single string located in A2, write this piece of code into the editor.

Step 3 – Run the code and convert one string to array 

– After writing the code into the editor, press CTRL+S so save the code.
– Now press F5 or click on the play button to run the code and convert the string to array as shown above.
– The working of code will be explained in the last section of this tutorial.

Step 4 – Write the code to convert whole range of Strings to Array

– To convert the single string located in A2, write this piece of code into the editor.

Step 5 – Run the code and convert whole range of strings to array

– After writing the code into the editor, press CTRL+S so save the code.
– Now press F5 or click on the play button to run the code and convert the strings in the range to array as shown above.
– The working of code will be explained in the last section of this tutorial.

Explanation of the code:

The main line of the code which does the conversion of a string to array is as follows;

SingleValue = Split(c.Value, “,”)The Split function of VBA splits the string stored in c.Value at each delimiter “,” and stores all values separately in the String Array SingleValue. In the first example, we converted only the first string from the range and displayed it in the next columns. While in the second example we used a loop to convert all the values in the data range and converted them to arrays.