How do I make text to columns automated?

%3CLINGO-SUB%20id%3D%22lingo-sub-897666%22%20slang%3D%22en-US%22%3EHow%20do%20I%20make%20text%20to%20columns%20automated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-897666%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%20-%20I%20will%20be%20doing%20some%20reporting%20and%20have%20one%20column%20with%20some%20data%20separated%20by%20commas%2C%20I%20need%20to%20separate%20the%20data%20into%20columns%20and%20then%20make%20that%20rule%20apply%20to%20any%20further%20data%20I%20add%20to%20the%20end%20of%20the%20list%20so%20that%20I%20don't%20have%20to%20manually%20convert%20text%20to%20columns%20each%20time%20I%20add%20more%20data%20into%20the%20column%20with%20the%20combined%20data.%20Is%20there%20a%20way%20of%20doing%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShakeh%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-897666%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902506%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%20text%20to%20columns%20automated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902506%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421508%22%20target%3D%22_blank%22%3E%40Shakeh93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20just%20so%20I%20understand%3A%3C%2FP%3E%3CP%3EFirst%20you%20have%20this%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20296px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136121iDC5BD25A4D6F890B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-10-09_17h02_05.png%22%20title%3D%222019-10-09_17h02_05.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Enext%20you%20want%20to%20have%20this%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20384px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136122iEF8C9677D830058C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-10-09_17h02_38.png%22%20title%3D%222019-10-09_17h02_38.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ethen%20I'm%20not%20sure%20I%20understand.%20Is%20the%20next%20step%20that%20you%20add%20more%20comma%20separated%20text%20in%20row%202%20like%20this%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20379px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136123iBE36521BA15AAF45%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-10-09_17h03_33.png%22%20title%3D%222019-10-09_17h03_33.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnd%20what%20would%20be%20the%20next%20step%3F%3C%2FP%3E%3CP%3EOption%201%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20707px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136124iCD8562B705669761%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-10-09_17h04_41.png%22%20title%3D%222019-10-09_17h04_41.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eor%20option%202%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20353px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136125i3E788D7E9BFCB978%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-10-09_17h05_11.png%22%20title%3D%222019-10-09_17h05_11.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902524%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%20text%20to%20columns%20automated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902524%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20thanks%20so%20much%20for%20replying.%20It%20would%20be%20option%202%20but%20I%20would%20like%20the%20text%20to%20automatically%20split%20into%20the%20columns%20without%20doing%20it%20manually%20each%20time%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422482%22%20target%3D%22_blank%22%3E%40PascalKTeam%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902699%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%20text%20to%20columns%20automated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902699%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421508%22%20target%3D%22_blank%22%3E%40Shakeh93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20use%20VBA%20to%20automate%20this%20task.%3C%2FP%3E%3CP%3EPlace%20the%20following%20code%20for%20Change%20Event%20on%20the%20Sheet%20Module%20and%20to%20do%20that%2C%20right%20click%20on%20the%20Sheet%20Tab%20--%26gt%3B%20View%20Code%20--%26gt%3B%20paste%20the%20code%20given%20below%20into%20the%20opened%20code%20window%20--%26gt%3B%20Close%20the%20VB%20editor%20and%20save%20your%20Workbook%20as%20Macro-Enabled%20Workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0ADim%20cel%20As%20Range%0AOn%20Error%20GoTo%20Skip%0AIf%20Target.Column%20%3D%201%20Then%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20For%20Each%20cel%20In%20Target.Cells%0A%20%20%20%20%20%20%20%20If%20cel%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20cel.TextToColumns%20comma%3A%3DTrue%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20cel%0AEnd%20If%0ASkip%3A%0AApplication.EnableEvents%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20for%20reference.%20The%20Sheet1%20Module%20has%20the%20above%20code.%3C%2FP%3E%3CP%3ETo%20test%20the%20code%2C%20place%20a%20comma%20separated%20list%20(e.g.%26nbsp%3BWord1%2CWord2%2CWord3%2CWord4)%20in%20any%20cell%20in%20Column%20A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-904326%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%20text%20to%20columns%20automated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-904326%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421508%22%20target%3D%22_blank%22%3E%40Shakeh93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20consider%20using%20Power%20Query.%20It's%20one%20single%20step%20for%20splitting%20at%20a%20Delimiter%20and%20send%20the%20data%20back%20to%20Excel%20.%20Sample%20attached.%3C%2FP%3E%3CP%3ESteps%3A%3C%2FP%3E%3CUL%3E%3CLI%3EConvert%20your%20list%20into%20a%20Table%20(CTRL%20%2B%20T)%3C%2FLI%3E%3CLI%3EClick%20on%20the%20Data%20Tab%20and%20Select%20%3A%20%22From%20Table%22%20%26gt%3B%26gt%3B%26nbsp%3BThe%20Query%20Editor%20Opens%3C%2FLI%3E%3CLI%3EOn%20the%20Home%20Tab%20Click%20on%20%22Split%20Columns%22%20%26gt%3B%26gt%3B%26nbsp%3BSelect%20By%20Delimiter%20%22%2C%20%22%20and%20each%20occurrence%3C%2FLI%3E%3CLI%3EOn%20the%20Home%20Tab%20%26gt%3B%26gt%3B%20Close%20and%20Load%3C%2FLI%3E%3C%2FUL%3E%3CP%3EWhen%20source%20data%20Changes%2C%20just%20hit%20Refresh.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136556i4C3F8906BEF06C2F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22PQ%20Split.png%22%20title%3D%22PQ%20Split.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BHope%20That%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2398762%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%20text%20to%20columns%20automated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2398762%22%20slang%3D%22en-US%22%3EShare%20that%20code%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902689%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%20text%20to%20columns%20automated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902689%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421508%22%20target%3D%22_blank%22%3E%40Shakeh93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I%20should%20have%20the%20solutions%2C%20please%20just%20look%20at%20attached%20video%20and%20tell%20me%20if%20that's%20OK%20so%20I%20can%20share%20the%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2398763%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20make%20text%20to%20columns%20automated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2398763%22%20slang%3D%22en-US%22%3EShare%20this%20code%3C%2FLINGO-BODY%3E
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