Delimit the cell values

[Kamalakar1980]

Hi Team,

I have a data like as below

R ID	R NAME
1001​	IT Dir Technical dir Telecommunications dir
1002​	Implementation mgr project mgr Programme mgr

Each cell value in the column "R NAME" is the combination of the roles.

Want to break this input into following output as below:

R ID	R NAME
1001​	IT Dir
1001​	Technical dir
1001​	Telecommunications dir
1002​	Implementation mgr
1002​	project mgr
1002​	Programme mgr

can you please help me here.

Regards,
Kamal M.

 

[bosco_yip]

What is your Excel version using now ? and

Which is your selected solution : Formula, VBA or Power Query ?

 

[Kamalakar1980]

Hi Bosco,

I am looking at the solution should be using excel formula or VBA

Regards,
Kamal M.

 

[bosco_yip]

Hi Bosco,
I am looking at the solution should be using excel formula or VBA
Regards,
Kamal M.

You still haven't replied my 1st question.

 

[Kamalakar1980]

You still haven't replied my 1st question.

I am sorry, Bosco

completely missed this question. I am using Excel 2019 version

Regards,
Kamal M.

 

[bosco_yip]

Try,

In D2, array formula ("Ctrl+shift+Enter") copied across and down :

=IFERROR(FILTERXML("<a><b>"&CONCAT(SUBSTITUTE($B$2:$B$3&CHAR(10),CHAR(10),"</b><b>"&$A$2:$A$3&"</b><b>"))&"</b></a>","a/b["&ROW($A1)*2-COLUMN(A$1)+1&"]"),"")

 

[Kamalakar1980]

Try,

In D2, array formula ("Ctrl+shift+Enter") copied across and down :

=IFERROR(FILTERXML("<a><b>"&CONCAT(SUBSTITUTE($B$2:$B$3&CHAR(10),CHAR(10),"</b><b>"&$A$2:$A$3&"</b><b>"))&"</b></a>","a/b["&ROW($A1)*2-COLUMN(A$1)+1&"]"),"")

View attachment 77840

Perfect !!!! Bosco

thanks a lot for your timely help...