Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Email Addresses from Contacts

Is there a way to convert / extract the email address from the values in a column set up as a contact? I have a need for the email address associated with contacts.

Thanks

Answers

  • Community Champion

    Hi @Matt Galindo

    There are three ways I know of. The first method is easy and recommended..😀

    Method 1: Change a contact list column's property to text/number

    Then, use the <email> part using the TEXT functions. The merit of this method is you get the email information of users not belonging to your organization.

    Changed the Contact List column property to Text/Number

    I changed the contact list column property back to the contact list.

    Method 2: User List report

    Go to the user management in Adamin Center and get the user list.

    The demerit of this method is you get information only from users in your organization.

    Method 3: Smartsheet API

    Use the List Contacts or List Users method. Then, compare with the Contact List's name value.

    https://smartsheet.redoc.ly/tag/contacts#operation/list-contacts

  • Community Champion

    Hi

    There is not a way to automatically extract the email address from a contact type cell without an App or API.

    However, if the list isn't changing too much, you can get the emails out manually without too much effort.

    1. Create a new column (Text/Number type). In the example I have called this "Text Contact".
    2. Select the contacts you want to get email addresses for.
    3. Copy and paste them to the new column. The contacts will appear as  NAME <EMAIL>.
    4. Create another new column (Text/Number type).
    5. Use a formula like the one below to strip out everything except the email address.

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



  • ✭✭✭✭

    Thank you both for these options. I'm sure one of these will work.

    Matt

  • @KPH Your solution works great, if you manually copy/paste the Contact to the new column (Text/Number). I've checked out other threads and I haven't seen a solution (yet) that you can do this automatically (e.g., create new Helper column that displays Name <Email>, then use your MID function to extract just the email. With this method, and not using a manual Copy/Paste, Smartsheet considers even this column as just a Name, no Email address.

    Has there been any more changes in Smartsheet that enable an ability to automatically extract the email from a Contact?

  • Community Champion

    Hi @aschneiderheinze1025

    Glad to hear the formula worked for you. I am not aware of any changes that remove the need for the manual step. You either need to copy the data to a new column or change the format of the contact list column - something to prevent Smartsheet from treating these as "contacts".

    I did have a little search around and it looks like a solution is on the wish list. You can vote and add your comment here: https://community.smartsheet.com/discussion/94024/get-email-from-contact-name?_gl=15ssbf4_gcl_auMTUyMDk5NTI5Ny4xNzM2MTIyMTI4_gaMTg1OTkyODkyLjE3MzYxMjIxMjg._ga_ZYH7XNXMZK*MTc0MDI3NDMxNS4xMy4xLjE3NDAyNzY4ODUuMTEuMC4w

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6