Is it possible to separate multiple answers in one column and make it into a bar graph?

[Maya2022]

Hello everyone! This is my first post so I'm sorry if I've made a mistake:

I'm trying to create some bar/line/pie charts within Excel using data from a survey. However, one of the questions that was imported over has multiple answers within the same category (see column E in fake sample photo). Is there an easy way to create a bar graph that shows the popularity of each answer within that answer? Or, alternatively, is there an easy way to recategorize the data in column E to separate each answer? My actual spreadsheet has too many responses to do easily one by one.

 

[JamesCanale]

No pineapple.

MrExcelPlayground8-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Age	Date	Pizza?	Toppings	Fav?	Split
2	john	12	1/1/2022	yes	Cheese, Pepperoni, Sausage	Cheese	Cheese	Pepperoni	Sausage
3	jane	21	2/2/2022	no	Cheese, Bacon, Olives, Spinach, Chicken	Bacon	Cheese	Bacon	Olives	Spinach	Chicken
4	Jamie	49	1/1/2021	Yes	Garlic, Pepperoni	Garlic	Garlic	Pepperoni
5	Nicole	21	4/5/2019	yes	Brocoli, Spinach	Spinach	Brocoli	Spinach
6
7
8
9					All Items	Count of people that like it
10					Cheese	2
11					Pepperoni	2
12					Sausage	1
13					Bacon	1
14					Olives	1
15					Spinach	2
16					Chicken	1
17					Garlic	1
18					Brocoli	1
Sheet31

Cell Formulas
Range	Formula
G2:I2,G4:H5,G3:K3	G2	=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(E2,", ","</y><y>")&"</y></x>","//y"))
E10:E18	E10	=UNIQUE(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(", ",TRUE,E2:E5),", ","</y><y>")&"</y></x>","//y"))
F10:F18	F10	=SUM(IF(ISERROR(SEARCH(E10,$E$2:$E$5)),0,1))
Dynamic array formulas.

Text Join might get problematic if the list gets really long (32000 characters). But if you have a complete list of possible toppings, you can skip the formula in E10.
Pie charts are most appropriate.

 

[Maya2022]

No pineapple.

MrExcelPlayground8-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Age	Date	Pizza?	Toppings	Fav?	Split
2	john	12	1/1/2022	yes	Cheese, Pepperoni, Sausage	Cheese	Cheese	Pepperoni	Sausage
3	jane	21	2/2/2022	no	Cheese, Bacon, Olives, Spinach, Chicken	Bacon	Cheese	Bacon	Olives	Spinach	Chicken
4	Jamie	49	1/1/2021	Yes	Garlic, Pepperoni	Garlic	Garlic	Pepperoni
5	Nicole	21	4/5/2019	yes	Brocoli, Spinach	Spinach	Brocoli	Spinach
6
7
8
9					All Items	Count of people that like it
10					Cheese	2
11					Pepperoni	2
12					Sausage	1
13					Bacon	1
14					Olives	1
15					Spinach	2
16					Chicken	1
17					Garlic	1
18					Brocoli	1
Sheet31

Cell Formulas
Range	Formula
G2:I2,G4:H5,G3:K3	G2	=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(E2,", ","</y><y>")&"</y></x>","//y"))
E10:E18	E10	=UNIQUE(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(", ",TRUE,E2:E5),", ","</y><y>")&"</y></x>","//y"))
F10:F18	F10	=SUM(IF(ISERROR(SEARCH(E10,$E$2:$E$5)),0,1))
Dynamic array formulas.

Text Join might get problematic if the list gets really long (32000 characters). But if you have a complete list of possible toppings, you can skip the formula in E10.
Pie charts are most appropriate.

I'm quite partial to pineapple on pizza myself, but I think that is an argument for somewhere else haha. Thank you so much for taking the time to show me this! It really made things 10x easier.

 

[JamesCanale]

There is a new function "TEXTSPLIT" and others coming to excel365 soon. It will handle these things more elegantly.