Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Formula to check if cell contains text string + value in another cell

edited 12/09/19 in Archived 2017 Posts

For example, the value in column 4 row 2 is "xxxxx_email [email protected]_xxxxx"

The value in column 2 row 2 is "[email protected]"

I need a formula that checks if column 4 row 2 contains "email [email protected]" by crosschecking the value in column 2 row 2 and using the text string "email address="

I have it worked out in Excel using a COUNTIF statement, but it does not seem to translate well into Smartsheet.



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Try this:

    =FIND("email address=" + [Col_2]2, [Col_4]2)

    where Col_2 and Col_4 are your columns 2 and 4

    This will return a non-zero number (likely based on your example) when it finds a match, 0 when it doesn't.

    You can wrap at in a COUNT or COUNTIF or IF or whatever you want to do.

    I hope this helps.


  • Thanks.  Is there a way to get the formula to show yes/no or true/false rather than a numeric value?

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    FIND() returns a number. In this case, we only care if it is 0 or not.

    =IF(FIND() > 0, "Yes", "No")


    =IF(FIND() > 0, "True", "False")

    or whatever your wish is.

    Replace FIND() with the formula from the previous response.


  • That is excellent, J. Craig Williams.  Thank you so much for your help!

  • What about if one wants to check for a number of values...so in this example, if email address, blah...if street address, blah blah...if phone number, blah blah blah.

    In my case I want to look at whether a project title includes "emails" "promo" "social" "web refresh" and/or others, to populate an hours column with (predetermined) number of hours for that activity...?

    Thanks very much in advance for the hand.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    I'd probably build a table and do a lookup using index(...,match(...))

    exact details would take a while to nail down, but it is possible.


  • Thanks muchly for the reply...but that sounds a bit over my head!




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    What I am missing to provide adequate help is more understanding of your current design and future needs/ requirements.

    If you have budget for support (ranging from mentoring to full design and implementation), reach out to Richard at Smarter Business Processses

    [email protected]

    We can help. First hour free.


  • Excellent, noted.



  • pgreene31
    edited 11/15/18

    Is there a "check" or formula to check to see if the value in a cell is the result of a formula, or text entered.

    Use example: I have dates calculated for a schedule. Once the date is "actual" I would like to override the formula with the actual date. I would like to set auto formatting, based on whether the valued is the result of a formula, or entered text.

    I'm hoping to avoid adding an additional column to capture actual date, but wouldn't mind adding checkbox column to indicate the value is text or formula, then i could set up auto formatting based on the checklist.

    I should note that I tried ISTEXT formula, but it didin't work on a date column. I also tried ISDATE, but it recognized both, the formula and the "entered date" as a date.

    Thanks for your help

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Patricia,

    Unfortunately, I don't think that is possible. You'll have to add a column to add another actual date.

    It could probably be automated with the help of a third-party tool like Zapier or similar.

    I hope this helps you!

    Have a fantastic weekend!


    Andrée Starå

    Workflow Consultant @ Get Done Consulting


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

This discussion has been closed.