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

 

Shakeh

7 Replies

@Shakeh93 

 

Hi, just so I understand:

First you have this

2019-10-09_17h02_05.png

next you want to have this

2019-10-09_17h02_38.png

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

2019-10-09_17h03_33.png

And what would be the next step?

Option 1:

2019-10-09_17h04_41.png

or option 2:

2019-10-09_17h05_11.png

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 

 

@Shakeh93 

 

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

@Shakeh93 

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.

 

@Shakeh93 

Hi 

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.

Steps:

  • 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