Extracting all instances of a word from a text string and creating a list in a separate cell

erober01

Hello all,

I have been pouring through excel and smartsheet help forums for a couple of days now, and I haven't seen my issue asked or addressed so thought I'd ask here.

I have, in one column called Terms, cells that contains lists of terms and phrases. For example, one cell contains the following text string: "Confidential Information, Authority, Trend Report, Confidentiality, Affiliate Report, Jenny's Confidential Log In". This cell is related to another cell in the same row, and each row's phrases are unique. In this example, this row would be associated with Jenny.

I have a search box in a separate sheet, where users can type in a specific word they are looking for within this column and an associated check box will check. For example, if someone were to type in Authority, each row where "Authority" is present in the "Terms" column would result in the checkbox being checked.

Some of my cells in the "Terms" column have more phrases and terms than are easy to sift through so a checkbox is not enough for users who want to see exactly what the phrase is that this word is in, so my specific question is: is it possible in smartsheets to extract multiple phrases in a single cell that contain a specific word and concatenate a list of those phrases in a separate cell?

For example: If someone were to type in the word "Confidential" in the search box, I would want a cell in Jenny's row to return "Confidential Information, Confidentiality, Jenny's Confidential Log In", having gone through the single cell in the "Terms" column that is associated with Jenny's row.

I have tried a lot of different find, replace, substitute, right left, mid and len combinations, but I cannot seem to figure out how to do this.

Thank you very much and please let me know if I can be more clear!

eric.o

Hello,

Currently, we don’t have a method to pull from a single cell all of the word phrases that contain one of the same words, such as "Confidential", when searching the one word. You may be able to achieve this by placing the singular desired words phrases into separate cells/columns this may allow you to create a formula to then pull all relevant information.

Cheers,

Eric

Smartsheet Support