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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!