How to extract the email address from column having Contact Type

manasaMS

Here in the "PM Name" column, I have a value which is of Contact type. I tried copying the value over to the next column, then i get the Email address Prefixed by my name(Manasa M S<manasa@gmail.com>).In the next column I want to extract only the email address(eg: manasa@gmail.com). please share if you have formula for this problem.

Paul Newcome

Try something like this:

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

RebSchr

Greetings,

I found this formula on another post long time ago and utilize it in my project sheets to get the email address's from my contact list. I did have to convert the contact into another column as a Text/Number in order for it to work. There might be an easier way, but this worked for me:

=MID(Contact List@row, FIND("<", Contact List@row) + 1, FIND(">", Contact List@row) - FIND("<", Contact List@row) - 1)

Hope this helps!

Rebekah Schreader

manasaMS

@Paul Newcome

Hi Paul,

Thanks for your comment. But Iam getting my name as the output instead of email address.

Please let me know if there is any way to directly extract only the email address without copying the data over to another column.

manasaMS

@RebSchr

Hi Rebekah,

Thanks for the formula, but I was looking to extract the email address without having to use an intermediate column.

Paul Newcome

In your initial screenshot you had the actual email address there in [Column3]. That is what the formula was written for. Not a cell containing just a name.

manasaMS

@Paul Newcome

Hi Paul,

Sorry I misworded earlier, I am looking to extract the email address from "PM Name" column which is of Contact type. thanks

Paul Newcome

You would need the API or possibly Bridge or some other third party app such as Zapier (maybe) to accomplish that.

Either that or you would need to create a reference table that contains everyone's names and emails then use a formula with cross sheet references to search the table and pull in the email based on the name.

Andi F

You have to convert the PM Name column type to column to a text/number. Then use the formula mentioned by RebSchr.

=MID(Contact List@row, FIND("<", Contact List@row) + 1, FIND(">", Contact List@row) - FIND("<", Contact List@row) - 1)

OR if you want to keep the original PM Name the way it is, you can create another column with the same list and follow the above steps. Once that's done you can hide it.

Hope this help.

Kevin7859

I have used this solution partially with success, but I also have partial contacts that are email address only within the list, how can I extract those since they are only comma separated and do not have the < > brackets?