Expanding a cell with comma-separated values to multiple different cells

[Caracarn]

Maybe I'm going about this the wrong way, but I'm trying to take a cell with comma-separated text in it and expanded it to multiple different cells in a column using formulas. I realize that I can use Data > Text to Columns, but I'd love for this to be automatic by formulas.

The whole goal of this is to be able to type in different meals into a spreadsheet under the Meal column. Then the spreadsheet will populate the ingredients from another spreadsheet (Where I have recipes already entered). Then it will take the ingredients column and expand it into one large list that would be easily printed to go shopping. I understand that this might not be the easiest way, or possible, but in my head, it sounded like a great idea.

So, is there a way to automate this process with formulas? or am I trying to do something that is impossible with formulas and I'll just have to use the Text to Columns feature. Thanks for any help that you can give me!

This is what I want to start with:

Meal	Ingredients
Pizza	Pizza, Breadsticks, Chips
Sandwiches	Bread, Tomatoes, Onions, Lettuce
Sloppy Joes	Meat, Sloppy Joe, Hamburger Buns, Onions, Pickles

This is what I want to end with:

Shopping List

Pizza

Breadsticks

Chips

Bread

Tomatoes

Onions

Lettuce

Meat

Sloppy Joe

Hamburger Buns

Onions

Pickles

 

[Peter_SSs]

Using your Excel 2021, try this

22 01 20.xlsm
	A	B	C	D
1	Meal	Ingredients		List
2	Pizza	Pizza, Breadsticks, Chips		Pizza
3	Sandwiches	Bread, Tomatoes, Onions, Lettuce		Breadsticks
4	Sloppy Joes	Meat, Sloppy Joe, Hamburger Buns, Onions, Pickles		Chips
5				Bread
6				Tomatoes
7				Onions
8				Lettuce
9				Meat
10				Sloppy Joe
11				Hamburger Buns
12				Onions
13				Pickles
14
List

Cell Formulas
Range	Formula
D2:D13	D2	=FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN(",",1,B2:B10),",","</c><c>")&"</c></p>","//c")
Dynamic array formulas.