CONTAINS: What's it all about and how does it work?

Paul Newcome

I personally am still getting used to the new CONTAINS function, and I have noticed a few others on here looking for clarity. I figured I would start this thread in an effort to hopefully combine knowledge and do some collective brainstorming on how it works and some good examples of use cases.

 

I'll be revisiting this thread later with some comments as I figure things out, and if anyone else has anything to add, feel free!

Andrée Starå

Hi Paul,

I added the below from the function list and from the announcement for the new functions.

https://help.smartsheet.com/function/contains

CONTAINS Function

Used within another function to search for a character or string. Returns true if found, false if not found.

Sample Usage

IF(CONTAINS("Jacket", [Clothing Item]:[Clothing Item]), "True", "False")

Syntax

CONTAINS (search_for, range)

• search_for — The text or a cell reference to find.

• range — The group of cells to evaluate.

=IF(CONTAINS("shirt", [Clothing Item]:[Clothing Item]),"True", "False")    

CONTAINS enables you to search for a character or string in a cell or cell range. It returns true if found, false if not found.

You can use CONTAINS if you need to know if there is a specific word, character, or an anomaly in the data. Just remember to put quotes around the string you are searching for, then the formula will search in text/number, dropdown, contact, or symbol columns (search for the underlying symbol name, like “red”).

Hope that helps!    

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Thanks, Andree!

 

Here's a few observations I've made during my own testing.

.

1. It will not find a value in a contact type column IF the option to have multiple contacts per cell is turned on AND you are using it within another function (such as a COUNTIFS). This is true even if there is still only one contact per cell. Toggling the option off will correct this.

=COUNTIFS(Email:Email, CONTAINS(Email@row, @cell))

The above should return at least a count of 1, but it will return a 0 if the multiple contacts per cell option is turned on.

.

2. It can actually be used as a standalone. It does not have to be included within a separate function just to run. If it finds the text you are looking for, it will return a true, otherwise a false.

 

If you were to use the function as a standalone, any column type that does not utilize a true/false output will give you the #INVALID COLUMN VALUE error. As a standalone, it will work in a checkbox type column or the two symbol columns that toggles either a flag or a star on or off.

.

3. When using it within another function such as an IF statement, you can leave it as a standalone in the logical_expression portion. You can also use 1/0 for true/false as well as the actual text of true or false. When using the actual text of true or false, you do not need to use quotation marks (you can if you want to but that only applies to text) and upper vs lower case doesn't matter.

 

True = true = TRUE = "TRUE" = "True" = "true" = 1

Note: "1" does not work for a true value.

 

False = false = FALSE = "FALSE" = "False" = "false" = 0

Note: "0" does not work for a false value.

Paul Newcome

HERE is a link to a thread that has some detailed notes on using CONTAINS in a COUNTIFS looking at a Contact type column.

Paul Newcome

Contains also does not pick up on numerical values... See screenshots below...

Paul Newcome

But... If you convert the LIST to text, it can find a number. HOWEVER converting the number you are searching for to text does not make a difference.

 

It looks like the CONTAINS function can only work on text RANGES but isn't too worried about the search value type.

Paul Newcome

CONTAINS also cannot find true/false values.

 

Using CONTAINS to look down a checkbox type column, no variation of true/false will work as the value you are searching for.

Jordi de Jong

Hi all,

Is it also possible to use this formula when you reference to an another sheet?

By example:

=COUNTIF({Sheet Range 2}, CONTAINS(cell2, {Sheet Range 2}))

Thanks in forward!

Jordi

Paul Newcome

@Jordi de Jong Yes! You would use an @cell reference.

=COUNTIF({Sheet Range 2}, CONTAINS(cell2, @cell))

Michael DiGioia

I am trying to have a row return multiple returns from another row Column 1 I have a drop down box with a handful of selections to choice from base on those selections I have column 2 needing to return a value from what is in column 1

Column 1 are alpha and column 2 numeric return I am needing

I cannot find a formula to work

Michael DiGioia

I am trying to have a row return multiple returns from another row Column 1 I have a drop down box with a handful of selections to choice from base on those selections I have column 2 needing to return a value from what is in column 1

Column 1 are alpha and column 2 numeric return I am needing

I cannot find a formula to work

Paul Newcome

@Michael DiGioia I'm not sure I understand what you are looking for. Are you able to provide a screenshot with some sample data?

Jonathan Delarosbil

The HAS function can be used in an IF function for detection of a single Contact in multiple contact list.

=IF(HAS([Colonne2]1; [Colonne2]2); "OUI"; "NON")

Paul Newcome

@Jonathan Delarosbil Yes. That is true. This thread was initially started to discuss the CONTAINS function though which leads me to note that CONTAINS does not work in Contact type columns.

Taima El Frieh

Hey Paul,

If I want to test a text that has (") within it, for example CONTAINS ("50" ",@cell) , I mean 50 inches. How would I do that?

Thank you.

Paul Newcome

@Taima El Frieh Do you HAVE to include the search on the quotation mark that designates inches? What is your context?