Split Text Data in Column by VBA Excel

split column data vba excel

Objective

To split the data in column using VBA Excel.

Approach

Here we wanted to split a single string in multiple strings. We had single space as delimiter. We have used the split function of VBA Excel.

Here we loop through each populated cell in column A, then apply split function on that particular cell value. We have stored split strings in column B and in subsequent columns according to number of split strings. We can see in the later part of this post how strings are stored in columns.

In the below image we can see that there were 4 strings in column A. Here, we can provide any string in column A, which can have multiple delimiters. It means that no matter how many delimiters we have in the string, this VBA code will split it correctly.

Before running the code, we had 4 strings in column A, here we have considered single space as delimiter in the strings.We can see how the data looked like before executing the VBA code.

split column data vba excel

 

Code

Sub SplitColumnData()

Dim LastPopulatedRow As Long, MyString As String
Dim StringPart() As String, ColIndex As Long
Dim MinArrSubscript As Integer, MaxArrSubscript As Integer

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

'Loop through each populated cell in column A
For RowIndex = 1 To LastPopulatedRow

'Split the cell value, here single space is the delimiter
StringPart() = Split(Range("A" & RowIndex).Value, " ")

MinArrSubscript = LBound(StringPart)
MaxArrSubscript = UBound(StringPart)

'Store each split string columns
For ColumnNum = MinArrSubscript To MaxArrSubscript
Cells(RowIndex, 2 + ColumnNum).Value = StringPart(ColumnNum)
Next ColumnNum
Next RowIndex

End Sub

 

 

Once we execute the VBA code, strings in all the cells have been split.You can see in the image below that all the string had different number of space in the strings.This functionality makes the code dynamic.Split data is stored from column B to E.

 

split column data vba excel

 

Post you may like

Create message box with yes no option in vba excel

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/lbound-function
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ubound-function