Are you able to pull emails from a Contact column?

RobbyW

I have a few sheets where I am needing to have the data Presented with the individuals name, then the email address in the column next to it.

I have come across a few articles that have suggested having a helper 'Contacts Sheet' to reference w/ =INDEX({Contacts Sheet Email}, MATCH{Name@row, {Contacts Sheet Name}, 0)) for other sheets, however, managing a Contacts Sheet is not realistic, or even possible in this instance.

I wasn't sure if there is a function that would be able to do this?

Thank you!

Darren Mullen

@RobbyW How dynamic is the data in the sheet? If it is static.... you can have another contact column, set it equal to the first, save the sheet, then convert the 2nd contact column to a text/number and it will show the name and email in the format NAME <EMAIL>, then you have to parse them into separate columns from there. As you probably already know, simply setting a text column equal to a contact column on brings in the name.

I'm not sure if this helps any... but you can go in the reverse direction easily with separate name and email columns into a contact column.

https://youtu.be/hTpXfl7musU

Paul Newcome

There is currently no function to be able to pull an email from a name in a contact type column. You would need to use the API or some other 3rd party app.

Darren Mullen

@RobbyW How dynamic is the data in the sheet? If it is static.... you can have another contact column, set it equal to the first, save the sheet, then convert the 2nd contact column to a text/number and it will show the name and email in the format NAME <EMAIL>, then you have to parse them into separate columns from there. As you probably already know, simply setting a text column equal to a contact column on brings in the name.

I'm not sure if this helps any... but you can go in the reverse direction easily with separate name and email columns into a contact column.

https://youtu.be/hTpXfl7musU

Paul Newcome

There is currently no function to be able to pull an email from a name in a contact type column. You would need to use the API or some other 3rd party app.

Tim C

My (not ideal) way around this is- in your sheet have the email be a text field instead of a contact column, then create a second column where you use a formula =email@row (whatever the original column is called) and the new contact column will just have the email.

Not perfect but works for my use case. Then you can use formulas to parse out first name, last name, domain name in separate columns if needed.

The more manual way is to double click on the cell of the contact column to "Edit Contact" that says the name, edit the cell and delete their name but not the email. (This has problems associated so only use it if you really have to and have the time)

Lauren Dominique

I've actually done this before, but it requires creating three helper columns.

First, create a duplicate of your contact column (let's call it "Helper-Contact"). Then switch the properties from contact to text. All of the contacts would then change to NAME <EMAIL> format.

Then, create a second helper column (we'll call it "Email") with text column properties.

Then, you'd create a third helper column (the times I've used this formula, I just call it "marker").

You'd then use the below formulas to pull over only the email from the Helper-Contact column as it's written within the <> into the Email column as text.

Formula for the marker column: =MAX(FIND("<", [Helper-Contact]@row), 2)

Formula for the Email column: =IFERROR(MID([Helper-Contact]@row, marker@row + 1, (LEN([Helper-Contact]@row) - (FIND("<", [Helper-Contact]@row))) - 1), "")

Paul Newcome

@Lauren Dominique Does this work on a recurring basis? Are you able to set this up and let it run as the sheet gets edited, or does it only work for the one time you do this and then you have to redo all of this every time a contact gets changed or a new row gets added?

Lauren Dominique

https://community.smartsheet.com/discussion/comment/422847#Comment_422847

It works for me on a recurring basis!

Paul Newcome

@Lauren Dominique How do you get it to continue working without having to reset anything? I tried using your method, and it doesn't pull an email address unless I manually copy/paste the contact into a second contact type column then manually change the column type to text/number.

Lauren Dominique

Oh, shoot. I now see what you mean... I can use it on a recurring basis because I have a separate "Contact" sheet that I maintain where I manually add a person's name and email each time they join the team (which only happens once in a while), which then feeds a helper column that combines their name and email in the NAME <EMAIL> format. And then I copy and paste that list of NAME <EMAIL>s into a single-select drop-down column in my sheet where I have these formulas I shared above.

It's been a while since we've had a new person join the team, so I forgot that key component. So sorry for misleading... I saw this thread and got excited thinking I could help.

Also, the reason I immediately said it works on a recurring basis is because sheet receives content from a form submission. So, every time someone selects a name on the form and it enters the sheet, it's already formatted the way it needs to be for the formula to separate out the email.

So... I guess I don't actually have a universal solution for this problem, but rather a workaround for a very specific/unique situation. Sorry again.

Paul Newcome

@Lauren Dominique No worries. I got excited too and was hoping I had just missed a piece.