Finding and returning values in a range of cells

erober01

Hi all,

I'm having a lot of trouble joining cells that contain a certain value (because there is no contain function). I have a range of cells that are all in a single row, and I'm trying to collect all of the cells that contain a certain value, and join them together.

For example, say I had a row with 4 columns that say "Cat","Umbrella Cat", "Dog","Frog", and I'm trying to isolate all of the columns that contain the word "Cat". If this formula worked like I want it to, I would use a combination of Join and Collect functions to return "Cat, Umbrella Cat" in a separate cell. This is as far as I've been able to get: JOIN(COLLECT([Row1]:[Row4],[Row1]:[Row4],="Cat"),", ").

That only gets me "Cat", not the other cell's value that contains the word Cat.

Does anyone have any suggestions on how to isolate and return cells that ~contain~ a certain value, not just equal it? It would be great if Smartsheets could create a contain function and/or allow us to be able to use "FIND" across a range of cells.

Thank you!

Paul Newcome

Use a FIND function for the word Cat. I would even recommend throwing in a LOWER function to make sure you grab every cell containing the word regardless of upper or lower case.

JOIN(COLLECT([Row1]:[Row4], [Row1]:[Row4],FIND("cat", LOWER(@cell))> 0),", ").

erober01

Hi Paul,

Thank you for this - I'm not sure if I'm missing something, but nothing comes up in the cell that I put this formula into. Would this formula search each cell? Or only the one cell I enter in the FIND formula?

Thank you,

Ellie

Paul Newcome

You would not specify a cell within the FIND function. The@cell引用指示检查中的每个细胞range.

In taking another look, it seems you are looking across multiple columns along the same row. If this is correct, you are also going to need to adjust your ranges. I am not sure what your exact column names are, but it would end up looking something like this (leave the@rowreference):

=JOIN(COLLECT([First Column Name]@row:[Last Column Name]@row, [First Column Name]@row:[Last Column Name]@row, FIND("cat", LOWER(@cell))> 0),", ")

erober01

Hi Paul,

THANK YOU I never knew that@cellwas a reference, not a way of saying [the cell you are looking for]. This makes everything so much easier! And it worked perfectly once I added@cellin.

Thank you so much for saving me hours of confusion

Best

Ellie

Paul Newcome

Happy to help!

And just for a little clarification... The@cellreference is more telling the formula to look at each cell within the range on an individual basis.