Forum Discussion
How do I make text to columns automated?
Hi there - I will be doing some reporting and have one column with some data separated by commas, I need to separate the data into columns and then make that rule apply to any further data I add to t...
Hi - thanks so much for replying. It would be option 2 but I would like the text to automatically split into the columns without doing it manually each time PascalKTeam
You may use VBA to automate this task.
Place the following code for Change Event on the Sheet Module and to do that, right click on the Sheet Tab --> View Code --> paste the code given below into the opened code window --> Close the VB editor and save your Workbook as Macro-Enabled Workbook.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
On Error GoTo Skip
If Target.Column = 1 Then
Application.EnableEvents = False
For Each cel In Target.Cells
If cel <> "" Then
cel.TextToColumns comma:=True
End If
Next cel
End If
Skip:
Application.EnableEvents = True
End Sub
Please find the attached for reference. The Sheet1 Module has the above code.
To test the code, place a comma separated list (e.g. Word1,Word2,Word3,Word4) in any cell in Column A.