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 reference indicates to check each cell within the 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 @row reference):

 

=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 @cell was 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 @cell in.

Thank you so much for saving me hours of confusion

Best

Ellie

Paul Newcome

Happy to help! 

 

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