Get Email from Contact Name

ker9

Formula or some option to get the email address associated with the Contact name. A name can be anything a user chooses (variable), but an email is not variable and can be used to do lookups more accurately than a name. (Relying on someone to type it in correctly is asking for trouble.)

I currently use DataMesh to get the information from the contact field, but it is slow (immediate update can take 5+ minutes).

Thank you.

(I searched Ideas and did not see this, apologies if it is a duplicate.)

ker9

Would love to hear Smartsheet's reasons for not resolving this simple problem. It has been requested for years.

ker9

A system formula that would lookup the contact from the user list to get the email WITHOUT having to download and import the list, which then needs constant maintenance (Enterprise Plan).

ro.fei

I've run into several situations where it would be really helpful to have a formula to take the email from a contact in a contact column. I've searched the Community & it seems there's currently no easy way to do this. This would really useful when trying to use Smartsheet to create distribution lists (or other situations where you want to be able to copy/paste email addresses).

ker9

@ro.fei

I started downloading our user list and then importing it to Smartsheet so that I can have cross-sheet formulas to get the email from the user name. I do this multiple times a week to ensure I have all new hires and remove exits. I create new automation for every import that will copy the rows to the existing sheet so none of the formulas on other sheets are impacted (copy is faster than move). Then I delete the old information (get the last row number, import/copy rows, delete rows from recorded last row # and up).

It's tedious and time consuming and should not be this difficult.

ro.fei

@ker9

I've actually been using a workaround from this discussion post from a while back. It also mentions your solution but I don't like the idea of having to constantly download the contacts list etc. so I used the solution where you change the contact list column to text/number & use a formula to extract the email. Then I just copy & paste the values (to replace the formulas) & switch the column type back to contact list. I'll quote it below:

Step 1a: Create a save as copy of existing sheet that you wish to download 

or

Step 1b: Convert the sheet contact list column to a text/numeric column; bingo!

(Note: Don't forget to convert it back to Contact List type]

Step 2: If Step 1a is followed then convert the "Contact List" column to a text/numeric column; bingo!

Step 3a: If Step 1a is followed then download the Smartsheet file and use excel based formulae to extract email address, within [abc <abc@abcxyzefg.com>]

Formulae: [=MID(A1, FIND("<", A1) + 1, FIND(">", A1) - FIND("<", A1) - 1) ]

or 

Step 3b: If Step 1b is followed then use below Smartsheet formulae to extract email address on Smartsheet another column (type text/numeric): 

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

Step 4: Job done!

chanmar

We are seeing a need for this when downloading/exporting sheets and reports to Excel. When exporting the sheets, if the contact has a First Name Last Name, it only shows that in the export instead of the email address. Converting and re-converting the columns is not an option for us (there's too many sheets to constantly do that).

Mattisphere

@ker9 These are the types of unfixed issues that really get me going. To your point, power users have been requesting this for a really long time. It's structured data where they clearly store the name and email separately (given the form) so there is no reason why they couldn't do this. Using a formula pointing at the contact column returns the name. Even if that returned the name and email, then we could at least parse the email address programmatically.

Sherry Fox

Great idea. And I would like to see a feature like this as part of Enterprise, as I do not have access to any of the Premium features.

jvanwho

+1 for being able to use a Contact field to match either on the display name or the email address.

KenR

I was simply trying to match a Created By field in one sheet to a contact field in another sheet. The match function failed because there was a name with the email in the contact field. If I removed the name it worked - but that defeats a benefit of the contact field being used elsewhere. My "solution" was to add another manual entry field for the email that some poor soul has to manage… sigh