Oct 07 2019 11:32 AM
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
Oct 09 2019 08:05 AM
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:
Oct 09 2019 08:12 AM
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
Oct 09 2019 09:03 AM
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
Oct 09 2019 09:18 AM
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.
Oct 09 2019 03:24 PM
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:
When source data Changes, just hit Refresh.
Hope That Helps
Nabil Mourad
Mar 04 2024 10:17 AM
Apr 02 2024 02:31 AM
@Subodh_Tiwari_sktneer hi, can you also help to revised the VB command if the data need to separate using text to column is hyphen (-) not comma.. TIA