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

MetroBOS
MetroBOS ✭
edited 12/09/19 in Archived 2017 Posts

For example, the value in column 4 row 2 is "xxxxx_email address=test@test.com_xxxxx"

The value in column 2 row 2 is "test@test.com"

I need a formula that checks if column 4 row 2 contains "email address=test@test.com" 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.

Tags:
· Share on FacebookShare on Twitter

Comments

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

    Craig

    · Share on FacebookShare on Twitter
  • MetroBOS
    MetroBOS ✭

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

    · Share on FacebookShare on Twitter
  • 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")

    or

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

    or whatever your wish is.

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

    Craig

    · Share on FacebookShare on Twitter
  • MetroBOS
    MetroBOS ✭

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

    · Share on FacebookShare on Twitter
  • gbgfootwear.pm38266
    gbgfootwear.pm38266 ✭

    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.

    · Share on FacebookShare on Twitter
  • 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.

    Craig

    · Share on FacebookShare on Twitter
  • gbgfootwear.pm38266
    gbgfootwear.pm38266 ✭

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

     

    Cheers,

    Suzanne

    · Share on FacebookShare on Twitter
  • 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

    richardr@smarterbusinessprocesses.com

    We can help. First hour free.

    Craig

    · Share on FacebookShare on Twitter
  • gbgfootwear.pm38266
    gbgfootwear.pm38266 ✭

    Excellent, noted.

    Cheers,

    Suzanne

    · Share on FacebookShare on Twitter
  • pgreene31
    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

    · Share on FacebookShare on Twitter
  • 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!

    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.

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories