IF CONTAIN formula - Email Validation

Monica Gallegos

I'm attempting what I'd consider a pretty basic IF CONTAINS formula, but I'm not getting the results I'm expecting.

As an attempt to validate emails, I'm trying to use IF CONTAINS to turn on a flag when .com is not found in the email cell.

"Your email" field contains email, which in input by the user via form.

"NEEDS ATTENTION" field is a flag with the below formula in it.

=IF(CONTAINS(".com", [Your Email]@row), 0, 1)

The intention...when Your email, contains ".com" the true value of 0 means no flag will be set. When the value doesn't contain a string with ".com", then the value will be 1 and flag will be set.

The challenge I'm running into is the formula I'm using doesn't seem to work as intended. I'm not sure what I'm missing. Any suggestions to correcting this formula or a more efficient formula to validate emails?

Paul Newcome

Try using a FIND function instead.

=IF(FIND(".com", [Your Email]@row) > 0, 0, 1)

Monica Gallegos

This worked perfectly! Thank you! Thank you! But why does FIND work but CONTAINS doesn't? Is there something specific in the sequence these formulas work?

Paul Newcome

CONTAINS doesn't work on contact type columns, but FIND does. It is something on the back-end in how the functions were programmed.

CONTAINS also cannot locate the "pm" or "am" in the system generated columns either.

Monica Gallegos

Oh I see. This is so very helpful! Thanks for this insight Paul!

Paul Newcome

Happy to help! 👍️

Tony Oxa

Hi @Paul Newcome i am trying to do the same thing. The difference is that i want to check if there is an email provided in a contact column. It does not work. #INCORECT ARGUMENT. I did try your suggestion.

Any thoughts?

Paul Newcome

@Tony Oxa Are you able to provide some screenshots with sample data for reference?

Tony Oxa

I want the formula to check if the "Employee e-mail" is empty or not in the "E-mail Provided" column. The "Employee E-mail" is a contact column.

I have tried the =IF(NOT(ISBLANK) funcion, but its says either incorrect argument or unparsable.

Andrée Starå

Hi @Tony Oxa

I hope you're well and safe!

Try something like this.

=IF([Employee e-mail]@row <> "", "YES", "NO")

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Tony Oxa

You should be able to use:

=IF([Employee e-mail]@row <> "", "YES", "NO")

Tony Oxa

Thanks on both of you, i was able to figure it out myself, they way i did it was i removed the not part, in the if value is true i put NO and if false i put YES so it worked. The problem now is that i have to validate if the email provided is an actual email, sometimes there is text on that column

Andrée Starå

@Tony Oxa

Excellent!

Happy to help!

One way to help is to use a formula with CONTAIN to check if there's an @ symbol.

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Tony Oxa

Thanks Andree,

And how would that formula look like ?

Andrée Starå

@Tony Oxa

Happy to help!

You would have to a so-called helper column that has to be set to text that would reference the main one, and then you'd check that one with the formula below.

It would look something like this in a Checkbox column. (it doesn't work in a contact column)

=(CONTAINS("@", [Employee e-mail (text only)]@row))

Make sense?

Would that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Tony Oxa

got you, that's fine i will restrict it on the form itself so it validates

Thanks alot