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

GeorgeB

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.

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)), ">", "")

GeorgeB

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

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.

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

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)), ">", "")

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