Copy cell contents in seperate cells

[lapta301]

Hi

I have a need for a formula I think

In a single cell I have loads of data that is separated by a comma and a space like:
GU1, GU2, GU3 1, GU3 2, KT7

In this example there are only 5 sets of data but in an extreme situation there can be many many more.

What I am trying to achieve is to copy each set seperated by a comma into its own cell without the leading space.

So that if the complete data is in I9 then GU1 with no space or comma goes into J9, GU2 goes into K9 and so on for as many sets of data is in the cell – could be 30

As ever any help would be most appreciated.

Thank you

 

[FormR]

Hi, take a look at this link:
http://www.excelfox.com/forum/showthread.php/333-Get-Field-from-Delimited-Text-String

For example:

Excel 2013/2016
	I	J	K	L	M	N
9	GU1, GU2, GU3 1, GU3 2, KT7	GU1	GU2	GU3 1	GU3 2	KT7
Sheet1

Cell Formulas
Range	Formula
J9	=TRIM(MID(SUBSTITUTE($I9,",",REPT(" ",99)),COLUMNS($J9:J9)*99-98,99))

 

[bneyazi]

One easy way is to select the column with all the data in it, then from Data tab in excel select Text To Columns and follow the on screen guide. You will probably need to comma separated option.

What text to column does it splits the data into separate columns depending the options you select. Make sure you have a few empty columns after column I.

I hope it helps.

 

[Scott T]

Excel 2010[TABLE="class: grid, width: 500"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]GU1, GU2, GU3 1, GU3 2, KT7[/TD]
[TD]GU1[/TD]
[TD]GU2[/TD]
[TD]GU3 1[/TD]
[TD]GU3 2[/TD]
[TD]KT7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J9[/TH]
[TD="align: left"]=LEFT(I9,SEARCH(",",I9)-1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K9[/TH]
[TD="align: left"]=TRIM(MID(SUBSTITUTE($I$9,",",REPT(" ",999)),COLUMNS($K$9:K9)*999,999))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Copy the formula in K9 across

 

[lapta301]

Thank you one and all for the replies which work fine.

FormR thank you also for the link which I will look at closely later on.

Again my sincere thanks to you all for taking an interest in my problem