Identifying and returning phrases within a single cell

erober01

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!

 

eric.o

Hello,

 

Currently, we don’t have a method to look into a single cell and pull out the specific values based on a word within the phrase, such as pulling the values "phone, cell phone, earphones" because they contain the word "phone",  but this will be considered as a possibility for future development.

 

Note: You can achieve this if they're in their own individual cells.

 

Cheers, 

Eric  

Smartsheet Support