How to extract email address from a contact list the use the value in an adjacent column

Up until now, I have copied/pasted the email address manually. Nevertheless, our database has grown so much. I've tried the below formula:

=IFERROR(SUBSTITUTE(SUBSTITUTE(MID([🎓 Student]@row, FIND("<", [🎓 Student]@row) + 1, FIND(">", [🎓 Student]@row) - FIND("<", [🎓 Student]@row) - 1), "<", ""), ">", ""), "")

but after hitting enter the value on the adjacent column appears blank.

{30F29E5B-30D0-4592-9443-E216801F4346}.png

Find more posts tagged with

Contacts

Accepted answers

kelly.huber

I was able to do this but had to add an additional column to the sheet. I have the contact column, text contact column, then a third to extract the email address. I populate the contact in the first column, do a simple copy/paste into the second (text), and have a column formula for the 3rd that extracts the email only using this:

=SUBSTITUTE(MID([Text Contact]@row, FIND("<", [Text Contact]@row) + 1, LEN([Text Contact]@row) - (FIND("<", [Text Contact]@row) + 1)), ">", "")

All comments

GeorgeB

Since my contact list format has two fields (first and last & email address):

{C24105C1-E2A8-4CD2-83CA-5033805EBAAC}.png

I've also tried the formula:

=IFERROR(MID([🎓 Student]@row, FIND("@", [🎓 Student]@row) - 1, LEN([🎓 Student]@row) - FIND("@", [🎓 Student]@row) + 2), "")

But the helper row comes out empty, blank.

{27BD9A5F-9682-445C-80BB-6C715EF0DC40}.png

Paul Newcome

What do you get when you remove the IFERROR?

GeorgeB

I get #INVALID VALUE, here is the formula I used REMOVING the IFERROR:

=MID([🎓 Student]@row, FIND("@", [🎓 Student]@row) - 1, LEN([🎓 Student]@row) - FIND("@", [🎓 Student]@row) + 2)

Paul Newcome

So the first thing I notice (and sorry for missing it earlier) is that the column you are referencing does not have an email address in it.

GeorgeB

I dont understand, I attached an image for reference of the contact list field and how it looks like

Paul Newcome

Right. And the [🎓 Student] column is displaying names. Your formula is written for a cell that will contain a string along the lines of

First Last <first.last@email.com.>

But your contact column just has

First Last

J.Naylor

I also need this feature. The Contact List column type does not provide an option to show only the email address or name and email address. There doesn't seem to be an option to extract the email address from the Contact List column. Using =ContactList@row only extracts the name. I've tried variations to try to get ContactList@row - NameOnly@row, and none have worked. SUM, COLLECT.

An opportunity to use a simple formula to extract the email from the contact field automatically would be a significant process improvement.

kelly.huber

=SUBSTITUTE(MID([Text Contact]@row, FIND("<", [Text Contact]@row) + 1, LEN([Text Contact]@row) - (FIND("<", [Text Contact]@row) + 1)), ">", "")

GeorgeB

Dear @kelly.huber, Thanks, and thanks; that's what I was looking for. Now, in pursuit of completely automating my process, do you know of any formula for copying/pasting from the contacts column to the text contact column?

Paul Newcome

There is no formula or automation to do that part. You would need to either manually do it or leverage the API, the premium add-on Bridge, or another third party app, but if you are going one of those routes, you may as well have it output the email address itself at that point.

GeorgeB

Thanks for claryfying @Paul Newcome

AsmaBens

@kelly.huber that worked perfectly! thank you!

johngraham78

@kelly.huber & @AsmaBens , one way to entirely automate this is use datamesh (if you have it) to perform the copy/paste operation of the contact field to the text field, then use the the formula to extract the email address. I'm perplexed why a simple formula will only extract the name and not name/email

Tommi

Is there already a feature request to allow email address to be extracted via formula only from a "contact" field?

This limitation is a problem for us in two use cases:

reporting/views where we need to see a contact's email address
automations where we need to include a contact's email address in a notification message, using the {{Some Contact Field}} notation.

thanks!

SSFeatures

Hi @Tommi, this feature request is exactly what you're looking for!

Best!

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.