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

MetroBOS

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.

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

MetroBOS

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

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

MetroBOS

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

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.

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

gbgfootwear.pm38266

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

 

Cheers,

Suzanne

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

gbgfootwear.pm38266

Excellent, noted.

Cheers,

Suzanne

pgreene31

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å

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