Snasui Excel Forum

Hi Dear,

I need your assistance how to write excel formula to split cells to many rows base on value separate by comma

I also provide a sample


Best wishes,

...

C2
=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1



A7:Axx
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($C$2:$C$4)/($C$2:$C$4>=COLUMN($A$2:$Z$2)),ROWS($A$7:A7))),"")


B7:Bxx
=IFERROR(TRIM(MID(SUBSTITUTE(VLOOKUP(A7,$A$2:$B$4,2,0),",",REPT(" ",99)),COUNTIF($A$7:A7,A7)*100-99,99)),"")

Norkaz

..

Without the helper, try another way like this.


A7:Axx
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$4)/(LEN($B$2:$B$4)-LEN(SUBSTITUTE($B$2:$B$4,",",""))+1>=COLUMN($A$2:$Z$2)),ROWS($A$7:A7))),"")


B7:Bxx
=IFERROR(TRIM(MID(SUBSTITUTE(VLOOKUP(A7,$A$2:$B$4,2,0),",",REPT(" ",99)),COUNTIF($A$7:A7,A7)*100-99,99)),"")

Norkaz

Another solution (Excel 2019+),

1. A7
   =IFERROR(INDEX(FILTERXML("<t><s>"&SUBSTITUTE(CONCAT(REPT($A$2:$A$4&",",LEN($B$2:$B$4)-LEN(SUBSTITUTE($B$2:$B$4,",",""))+1)),",","</s><s>")&"</s></t>","//s"),ROWS(B$7:B7)),"")
   Enter > Copy Down
2. B7
   =IFERROR(INDEX(FILTERXML("<t><s>"&SUBSTITUTE(CONCAT($B$2:$B$4&","),",","</s><s>")&"</s></t>","//s"),ROWS(B$7:B7)),"")
   Enter > Copy Down

Thank you in both for solutions