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.

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

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"

All comments

Jeff Reisman

Instead of using FIND, use the CONTAINS function. CONTAINS is not case-sensitive. That would account for "@xxx.com", "@XXX.com", "@Xxx.CoM", etc.

CONTAINS Function | Smartsheet Learning Center

Shawn Clemmons

https://community.smartsheet.com/discussion/comment/315059#Comment_315059

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.

Jeff Reisman

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"

Shawn Clemmons

Jeff you are a lifesaver! That worked! I appreciate your help with this. Take care.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315094#Comment_315094

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.