Identifying and returning phrases within a single cell
Hi all, I have been trying to figure it out alone but I thought I'd engage the community to see if anyone can help! I have a column in my spreadsheet that contains phrases separated by commas - for example (though with different words), in one cell it says "Phone, computer, mouse, cell phone, water bottle, earphones, bag." I am trying to create a formula that will isolate and return only phrases (separated by commas) that contain a specific word. For example, if I was looking for any phrase that contains "phone," this new cell would return "phone, cell phone, earphones."
Is this possible? I have been able to count the number of times a specific word shows up in a cell: =(LEN([Cell containing all phrases]) - LEN(SUBSTITUTE([Cell containing all phrases], [word I'm looking for], ""))) / LEN([word I'm looking for])
^including this because I've seen others ask for it in separate posts.
I also have been able to return the word at its first instance within the cell, however I cannot return the full phrase the word is associated with nor can I return all phrases containing that word separated by commas.
It definitely will be an iterative process based on the number of times the word shows up but I don't quite know how make a function that does that.
Any help would be greatly appreciated! Thank you!