How do I make text to columns automated?

New Contributor

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 the end of the list so that I don't have to manually convert text to columns each time I add more data into the column with the combined data. Is there a way of doing this? 


Please help!!



7 Replies



Hi, just so I understand:

First you have this


next you want to have this


then I'm not sure I understand. Is the next step that you add more comma separated text in row 2 like this?


And what would be the next step?

Option 1:


or option 2:


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 




I think I should have the solutions, please just look at attached video and tell me if that's OK so I can share the file


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
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.




You might consider using Power Query. It's one single step for splitting at a Delimiter and send the data back to Excel . Sample attached.


  • Convert your list into a Table (CTRL + T)
  • Click on the Data Tab and Select : "From Table" >> The Query Editor Opens
  • On the Home Tab Click on "Split Columns" >> Select By Delimiter ", " and each occurrence
  • On the Home Tab >> Close and Load

When source data Changes, just hit Refresh.

PQ Split.png

 Hope That Helps

Nabil Mourad

Share that code
Share this code