Nested IF FIND formula
I am trying to use an IF/FIND formula to validate a columns value to make sure it is a "valid" email address. It must contain "@xxx.com". I was able to find a solution to work with that scenario. However, I have discovered that if a user puts the email address in capital letters, it won't catch that. So "@XXX.COM" would be flagged as not a proper email format.
Here is the formula I am using that I was able to find here in the Smartsheet community originally.
=IF(FIND("@xxx.com", [Applicant Email Address]@row, 1) > 0, 0, 1). Formula works well for just the one variation.
I need to have a formula that will look at the column and determine if it contains either of those two options (lowercase or uppercase).
Is there a way to nest an IF/FIND formula to accomplish this? Any help is appreciated.
Best Answer
-
Get rid of that comma and "1" after the cell reference:
=IF(CONTAINS("@xxx.com", [Applicant Email Address]@row
, 1), 0, 1)Should be:
=IF(CONTAINS("@xxx.com", [Applicant Email Address]@row), 0, 1)
My formula returns a 1 for both "@TEST.COm" and "@test.com"
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Instead of using FIND, use the CONTAINS function. CONTAINS is not case-sensitive. That would account for "@xxx.com", "@XXX.com", "@Xxx.CoM", etc.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
I apologize, I'm not the greatest at these formulas. I did try to use the CONTAINS function and I am getting the error "INCORRECT ARGUMENT SET". I'm sure it's something simple I am not doing correctly. Here is what I put into the formula.
=IF(CONTAINS("@xxx.com", [Applicant Email Address]@row, 1), 0, 1)
I also tried putting quotations around the zero and one, but that didn't seem to do anything.
-
Get rid of that comma and "1" after the cell reference:
=IF(CONTAINS("@xxx.com", [Applicant Email Address]@row
, 1), 0, 1)Should be:
=IF(CONTAINS("@xxx.com", [Applicant Email Address]@row), 0, 1)
My formula returns a 1 for both "@TEST.COm" and "@test.com"
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Jeff you are a lifesaver! That worked! I appreciate your help with this. Take care.
-
Happy I could help!
I did notice one thing, just an FYI for all the people who look at this thread later for answers...
When I used this formula where my email addresses were in a column set as a Contact List column, it failed to find the search text, even when it matched exactly (i.e. looking for "@test.com" in "jeff@test.com".) It returned a 0 instead of 1 for both rows. When I switched the column back to Text/Number, it worked, finding the rows with "jeff@TEST.COm" and "jeffR@test.com".
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!