CONTAINS to check box if cell has certain email domain

Hi All, I have a project plan with a column called "Owner" that has a column type of contact list. The contacts are from multiple companies. It's a large project plan. I have created columns that have a column type of checkbox. I want to check the box if the owner is from a certain company (which can be determined by the email address from the contact list.

I first tried =CONTAINS("co01.com",Owner@row)

Then I tried =IF(CONTAINS("co01.com", Owner@row), 1, 0)

Also played with some variations but nothing is working. I'm probably missing something simple or maybe there is a better function to use. Any help appreciated.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Is the email displayed, or is it set as a contact type column with only the names displayed?

sherry17

Thanks for replying. It displays as the name for 2 companies and hovering over displays both name and email. for the other 2 companies, it displays the email address.

nicksnav

Hi @sherry17

From what I've been able to find the Contains function may not work well with contact columns.

A solution I have found looks like making a [Helper] column next to the contact cell with formula

=LEFT(Owner@row , LEN(Owner@row ) - 4). You can hide it to make it look nicer.

This will extract all characters without the last four characters (.com, .net) Then you can use your Contains function on the [Helper] column.

=IF(CONTAINS("co01", Helper@row ), 1, 0)

@Paul Newcome may have a better solution, but this is one I thought of and found that works.

Have a great day!😁

Paul Newcome

So CONTAINS doesn't normally play well with contact columns. Generally speaking, you would want to use the FIND function. But that also only works if you have the email address listed.

To do this, you will need all cells to contain the email address. How you achieve that is up to you, but once there, you would use a FIND function.

=IF(FIND("yahoo", [Email Column]@row) > 0, 1)

sherry17

Thanks, it worked for the contacts where the email is displayed in the Owner column but not for the name is displayed and the email is only seen when hovering over the owner name. Appreciate you trying to help.

sherry17

Paul, appreciate the help. Sounds like i need to find a way to populate a column with email addresses. Not sure how I'll do that but thanks for giving me some direction for a path forward.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.