Splitting data delimited by comma into different cells

[Johnmus]

Hi all, hope anyone can help me with this. I have three names separated by comma in one cell C11 (John Doe, John Smith, Joe Strong). I would like to split the names and have them transferred to cell B8, B9, B10 on a different sheet (from Fest Info to Front). So John Doe will end up in B8, John Smith in B9 etc.

Thanks for your help!

 

[jtakw]

Hi,

A little tired and late where I am, but this should help you:

Book1
	B	C	D
10
11		John Doe, John Smith, Joe Strong
12
Fest Info

Book1
	A	B	C
7
8		John Doe
9		John Smith
10		Joe Strong
11
Front

Cell Formulas
Range	Formula
B8	=LEFT('Fest Info'!C11,FIND(",",'Fest Info'!C11)-1)
B9	=TRIM(MID('Fest Info'!C11,FIND(",",'Fest Info'!C11)+1,FIND("/",SUBSTITUTE('Fest Info'!C11,",","/",2))-(LEN(B8)+2)))
B10	=TRIM(RIGHT('Fest Info'!C11,LEN('Fest Info'!C11)-FIND("/",SUBSTITUTE('Fest Info'!C11,",","/",2))))

 

[István Hirsch]

Enter this into Front B8 and copy down:

=TRIM(MID(SUBSTITUTE(", "&'Fest Info'!$C$11,", ",REPT(" ",100)),ROWS($B$1:B1)*100,100))

 

[Johnmus]

Enter this into Front B8 and copy down:

=TRIM(MID(SUBSTITUTE(", "&'Fest Info'!$C$11,", ",REPT(" ",100)),ROWS($B$1:B1)*100,100))

Both work great! Thank you for your help! Much appreciated.

John

 

[István Hirsch]

You are welcome.