Converting semicolon separated column values to individual rows

[shikarishambu]

I have a spreadsheet that lists entity and its users

Entity Name Users
XYZ Entity abc; def; ghi; ...

I want to convert this to
Entity User
XYZ Entity abc
XYZ Entity def
XYZ Entity ghi

Is there a way to do it in Excel?

 

[bosco_yip]

Perhaps,

1] In D2, copied down :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($2:$3)/((LEN($B$2:$B$3)-LEN(SUBSTITUTE($B$2:$B$3,";",""))+(RIGHT($B$2:$B$3)<>";"))>=COLUMN($A:$J)),ROWS($1:1))),"")

2] In E2, copied down :

=IF(D2="","",TRIM(MID(SUBSTITUTE(";"&VLOOKUP(D2,A$2:B$3,2,0),";",REPT(" ",99)),COUNTIF(D$2:D2,D2)*99,99)))

Regards
Bosco

 

[vletm]

shikarishambu
Open file
and
Press [ Do It ]

 

[shikarishambu]

Thanks everyone. I was able to use PowerQuery and do it.