How to split data into different columns using macro or VBA code in Excel. Watch the video below:

We can separate data like ‘Sammy Davis Jr’ in one one cell into three different cells or columns manually by using the ‘Text To Columns’ feature in Excel.

Let’s say we have the data ‘Sammy Davis Jr’ in Range A1. After selecting Range A1, we click on the ‘Data’ tab and from the ‘Data Tools’ group we click on ‘Text to Columns’. See image below.

A new window called ‘Conert text to Columns Wizard – Step 1 of 3’ pops up. See image below

We select ‘Delimited’ below the label: Choose the file type that best describes your data. In the step 2 we select the check-box with the caption ‘Space’ because our data is separated by spaces. We can see a preview of the result of our selection below. We also ensure that the checkbox next to the label: ‘Treat Consecutive delimiters as one’ is also selected. You will note that other delimiters like ‘Tab’, ‘Semicolon’, ‘Comma’, etc are also available. We now click on next and we are brought to step 3 of 3. Now we need to click finish. As soon as we do this, our data is separated into 3 different columns in Ranges A1, B1 and C1 as depicted below

Can we automate the process of ‘Text To Columns’? Yes, by using a macro or VBA code. The complete code is given below:

Sub MyTextToColumns()
ActiveSheet.UsedRange.Offset(1, 0).Select
Selection.TextToColumns Destination:=ActiveSheet.UsedRange.Offset(1, 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False

End Sub

In fact, we can perform such a text to columns action on dates too. The VBA code is given below:

Sub MyTextToColumns()
ActiveSheet.UsedRange.Offset(1, 0).Select
Selection.TextToColumns Destination:=ActiveSheet.UsedRange.Offset(1, 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=True, _
OtherChar:=”/”
InsertLabels ‘ we call this macro to insert appropriate labels in our worksheet

End Sub

Sub InsertLabels()
Dim row As Long, colimn As Long

Cells(1, 2) = “Month”
Cells(1, 3) = “Day”
Cells(1, 4) = “Year”
End Sub

The result of the above two macros is depicted in the image below:

Further Reading:

How to split an Excel cell using the Text to Columns function