CONTAINS: What's it all about and how does it work?
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!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
HERE is a link to a thread that has some detailed notes on using CONTAINS in a COUNTIFS looking at a Contact type column.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Contains also does not pick up on numerical values... See screenshots below...
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
-
@Jordi de Jong Yes! You would use an @cell reference.
=COUNTIF({Sheet Range 2}, CONTAINS(cell2, @cell))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
-
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'm not sure I understand what you are looking for. Are you able to provide a screenshot with some sample data?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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")
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
@Taima El Frieh Do you HAVE to include the search on the quotation mark that designates inches? What is your context?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!