To split the data in column using VBA Excel.
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.
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.
Post you may like