How do I make text to columns automated?

Copper 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

8 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
This is the best thing ever. Thank you so much for posting this code. It is so easy to implement.