Identifying and returning phrases within a single cell

erober01
erober01
edited 12/09/19 in Formulas and Functions

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!

 

Comments

  • eric.o
    eric.o Employee

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!