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

Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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!

CERTIFIED SMARTSHEET PLATINUM PARTNER

10xViz.com

Tags:
«1

Comments

  • Andrée Starå
    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

    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.

  • Paul Newcome
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Comm.PNG

    Comm1.PNG

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    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.

    Comm.PNG

    Comm1.PNG

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    @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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Michael DiGioia
    edited 10/14/20

    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

  • Paul Newcome
    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?

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    @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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!