Formula to combine Name and email to create a contact row

Hello,

I have a very long smartsheet list of names and emails in seperate columns. I want to join them to create a new column that turns them into a contact without having to fill out the contact card for each person.


I tried =Join({name}+" "+{email}) and it did join them, but did not turn into a contact.

Help please!

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Christine,

    As you've found, that formula would join the content together but the Contact List type of column won't be able to recognize what the name is and what the email is. There currently isn't a way for a formula to create contacts in the way you're looking to do (associating a name and an email together for your contact list).

    You could either simply turn the Email column into a Contact Type column, which would then recognize the email as a Contact.

    However if you're looking to associate names and emails together, you can bulk-import contacts to your "My Smartsheet Contacts". If you export the document with the names and emails to a CSV file, then you can import it back in, following the steps here: https://help.smartsheet.com/articles/796143-managing-contacts

    Cheers!

    Genevieve

  • @Genevieve P I have the same issue as the OP. I wanted to follow up on your answer and see if you can clarify.


    I know that I can import contacts from a csv file into SS. This will create the contacts in my SS account. But how do I then add those contacts to a sheet - in BULK. I currently have 1,000 contacts that I need added to a contact list within a specific sheet. I know I can import them to the SS account but is there a way to then create a contact list for a specific sheet with all those contacts? I mean besides having to type them in one by one in the column properties, since that would take forever.

    Thanks!!!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Melisa Dannhauser

    If the contacts are in your My Contact list, then they will already be available as an option to select from a Contact column in any sheet you create (as long as the column is not restricted to specific values).

    If you start to type a contact's name or email into a Contact Column, the email address & contact from your My Contact list will pop up as you type without it needing to be in the column properties. (Column properties will allow you to create a drop-down list with specific names visible immediately, or to restrict the column to certain contacts.)

    Alternatively, you can click on the little person icon, which will bring up a pop-up window showing all the contacts to choose from. This icon:

    Our Help Center has more information on the Contact Type of column, see here.

    Let me know if this helped answer your question! If not, it would be useful to know why you need to add 1,000 contacts to the column properties to see if there are alternate solutions.

    Cheers,

    Genevieve

  • Stefano Ferretti
    Stefano Ferretti ✭✭✭✭

    Hi Christine Hunter,


    I may have a solution when you have a long list of names and email address on different columns and want to make them into contacts.


    Step 1 (if those contacts are not in your Smartsheet contact list already -> create a .csv out of the 2 columns (Name and Email Addres) and import them into your Smartsheet Contact

    Step 2 : Make your column where you want the "Contact List" to appear as a "Text Column" , then apply the formula

    =Name@row+" <"+[Email Address]@row+">"

    expand to the whole column, and you'll end up with a column filled in with joined Name <email address> .

    lastly, change this column property to 'Contact List'

  • Kyle Walker
    Kyle Walker ✭✭✭✭
    edited 10/21/21

    I'm having a problem, where every time I reopen the sheet the contact column is broken and has to be fixed by changing is back to Text and then back to contact. Is that normal?

  • The former solution works, however I second @Kyle Walker. The column breaks and I need to turn column to text and them back to contact for it to work.


    Any workaround to this is appreciated!

  • Stefano Ferretti
    Stefano Ferretti ✭✭✭✭

    @Nirmal U @Kyle Walker @Christine Hunter


    I have found a workaround to the workaround issue ... you're not gonna like it :p

    The issue seem to be related to the fact that we create the contact column using a formula.

    if you use Data Mesh to 'copy' the Contact column into another column (Contact 2) in the same sheet (you can use the contact column as your unique value) run the mesh, you can then make Contact 2 column as a Contact List, and that seem to stick to the correct format when you close and reopen the sheet.


    Not ideal, but what can you do? :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!