Split Data into Multiple Columns with Excel VBA

Problem Scenario:

Let’s say you have a data set in Excel where values are separated by a delimiter (for example, commas, spaces, or semicolons) in a single column, and you want to split this data into multiple columns.

For example, you may have a single cell in column A with data like:

John, Smith, 28, New York

You want to split this into multiple columns (B, C, D, E) like this:

Solution: Using VBA to Split Data into Multiple Columns

We’ll use Excel VBA to automate the splitting process. Here’s a detailed code along with an explanation:

VBA Code to Split Data into Multiple Columns

Sub SplitDataIntoColumns()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim delimiter As String
    Dim i As Long
    Dim dataArray As Variant   
    ' Set the worksheet where the data is stored
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the delimiter that separates the values in the cell (e.g., comma, space, etc.)
    delimiter = ","   
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row   
    ' Loop through all rows with data
    For i = 1 To lastRow
        ' Read the cell value in column A and split it into an array using the delimiter
        dataArray = Split(ws.Cells(i, 1).Value, delimiter)       
        ' Loop through the array and place each value in the appropriate column
        For j = LBound(dataArray) To UBound(dataArray)
            ws.Cells(i, j + 2).Value = Trim(dataArray(j)) ' Place in column B, C, D, etc.
        Next j
    Next i   
    ' Notify the user that the operation is complete
    MsgBox "Data split successfully!", vbInformation
End Sub

Explanation of the Code

Let’s break down the code and explain each part:

1. Worksheet Setup

Set ws = ThisWorkbook.Sheets(« Sheet1 »)

This line defines the ws variable as the worksheet « Sheet1 ». You can change « Sheet1 » to the name of the sheet where your data is located.

2. Define Delimiter

delimiter = « , »

Here, we specify the delimiter (e.g., comma) that separates the data in the cells. If your data is separated by spaces, you would change this to  » « . Other delimiters can be used, such as semicolons or tabs, depending on your data.

3. Find the Last Row

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

This code finds the last row with data in column A. It looks from the bottom of the worksheet upwards and stops at the last filled cell in column A. This ensures we loop through all rows with data.

4. Loop Through All Rows

For i = 1 To lastRow

Here, we start a loop that runs through every row from 1 to the last row (found in the previous step). For each row, the value in column A will be split into an array.

5. Splitting Data

dataArray = Split(ws.Cells(i, 1).Value, delimiter)

For each row in column A, we get the value and split it using the Split function. The Split function breaks the text in the cell into an array based on the delimiter you defined. The dataArray now holds the individual components of the text (e.g., « John », « Smith », « 28 », « New York »).

6. Placing Data into Columns

For j = LBound(dataArray) To UBound(dataArray)

    ws.Cells(i, j + 2).Value = Trim(dataArray(j))

Next j

We then loop through the dataArray (the array containing the split data). The LBound function gives the index of the first element in the array, and UBound gives the index of the last element.

• We place each item from the array into the corresponding column, starting at column B (which is column 2 in VBA, hence j + 2). For example:
  • If the first value of the array is « John », it goes into B1.
  • The second value, « Smith », goes into C1, and so on.

The Trim function ensures that any extra spaces around the data are removed before placing it in the cell.

7. End of Loop and Message

Next i

MsgBox « Data split successfully! », vbInformation

After processing all rows, the loop ends, and a message box pops up to notify the user that the process is complete.

Customizing the Code

• Change the delimiter: If your data uses a different separator, simply change the delimiter variable. For example, for a space, you can use delimiter =  » « .
• Dynamic column handling: The code splits the data into columns starting from column B, but if you want to start from another column or handle more dynamic cases, you can modify the starting column dynamically.

Example Input and Output

Input (Column A):

Output:

Conclusion

This code provides a flexible solution to split data into multiple columns based on a delimiter. By adjusting the delimiter, you can easily adapt this code to different data structures.