Help Parsing up to 6 names in cell using formula

[maximillianrg]

Hello Excel Masters

Cell A1 = Jim Bean; Johnnie Walker; Jose Cuervo; Captain Morgan; Jameson Irish Whisky; Remy Martin


Q1 What formula could I put in cell A3 that would only pull the text "Jim Bean" from cell A1?
Q2 What formula could I put in cell A4 that would only pull the text "Johnnie Walker" from cell A1?
Q3 What formula could I put in cell A5 that would only pull the text "Jose Cuervo" from cell A1?
Q4 What formula could I put in cell A6 that would only pull the text "Captain Morgan" from cell A1?
Q5 What formula could I put in cell A7 that would only pull the text "Jameson Irish Whisky" from cell A1?
Q6 What formula could I put in cell A8 that would only pull the text "Remy Martin" from cell A1?

In the above formula spaces cannot exist before or after the result

Thank you so much in advance for your help

Max

 

[lrobbo314]

One way using VBA.

Book10
	A
1	Jim Bean; Johnnie Walker; Jose Cuervo; Captain Morgan; Jameson Irish Whisky; Remy Martin
2	Jim Bean
3	Johnnie Walker
4	Jose Cuervo
5	Captain Morgan
6	Jameson Irish Whisky
7	Remy Martin
Sheet5

Cell Formulas
Range	Formula
A2:A7	A2	=splitn($A$1,ROW(A1))

Function SPLITN(s As String, n As Integer)
SPLITN = Split(s, "; ")(n - 1)
End Function

 

[lrobbo314]

Or just using sheet formulas.

Book10
	A
1	Jim Bean; Johnnie Walker; Jose Cuervo; Captain Morgan; Jameson Irish Whisky; Remy Martin
2	Jim Bean
3	Johnnie Walker
4	Jose Cuervo
5	Captain Morgan
6	Jameson Irish Whisky
7	Remy Martin
Sheet5

Cell Formulas
Range	Formula
A2:A7	A2	=INDEX(FILTERXML("<a><b>" & SUBSTITUTE($A$1,"; ","</b><b>") & "</b></a>","//b"),ROW(A1))

 

[maximillianrg]

Hello Irobbo314

Thank you for the above solution and it worked perfectly. With this info I am now able to copy a cell full of one names to another cell, parse out the data, run a vlookup against each name, and convert employee names to numbers. You just saved me hours and hours of work