Comma separated value to columns in Excel -

i have multiple comma separated rows in excel drugs name e.g.

1) drug1,drug2,drug3,drug4

2) drug1,drug2,drug3,drug5

3) drug1,drug2,drug5,drug6

what want make these rows column such these rows distinct values becomes column. in our case drug1,drug2,drug3,drug4,drug5,drug6 become column , in rows should have 0 if row doesn't have drug , 1 if has drug in row.

any appreciated.

here's screenshot of how in multiple steps.

1. split data text columns (data menu, text columns)
2. copy data single column (b) (copy range b2:e4) b10, copy out of c10-end of range , put b13 etc) (and have 2 columns 1 rowid , 1 value
3. for rows 10,11,12 manually 1-3. copy , paste using ctrl-down arrow fill in each series until filled in .
4. generate pivot table (layout included now.)

this assumign 1 time thing , throw away. if has done repeatedly i'd macro it.