#### 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

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.

Tags:

## Comments

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭✭✭

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

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

Cheers,

Suzanne

• ✭✭✭✭✭✭

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.

Craig

• Excellent, noted.

Cheers,

Suzanne

• 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

• ✭✭✭✭✭✭

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:[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.