Extracting email information from a contact column

David Dolch

I have a need to extract email information from the information in a contact column. My users occasionally need to copy and past email information into other applications.

I know that if I reference a contact column into a text column that the name comes over as text.

I need a way to do the same thing, but have the email address come over as text

I know that the form filler does this (and some people have issues with it). I want to be able to do this on a sheet.

And no, a VLOOKUP that references a master list of Contacts and emails is not an option. We are talking about tens of thousands of potential names. It would be a full time job just to maintain such a list.

I was hoping there was a formula. Something like =EMAIL[Contact]@row. That would be perfect, but I don't think it exists.

Paul Newcome

There is currently not an option to do this within Smartsheet. There may be a way to do it through the API or another 3rd party application, but @Andrée Starå is more familiar with those than I am.

Feel free to Submit a Product Enhancement Request when you are able.

Samuel Mueller

This would be very helpful to extract contact details with a formula in sheets. Email, Job Title, etc.

Dan123

Agreed. I'd leverage a feature like this to create filters for each contractor my organization employs.

So a filter looking for "@company1.com" would only show rows assigned to joesmith@company1.com and jillsmith@company1.com

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/422848#Comment_422848

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.

A.J.

While there is a manual component here, this is incredibly helpful!