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!
Comments
-
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,
EricSmartsheet Support
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!