Extracting emails (Multiple) from a contact list

Options
Nicole V
Nicole V ✭✭✭
edited 06/08/23 in Formulas and Functions

Hi everyone,

There are two great formulas I have found that work on a contact column if it is converted to text. But it only works on the first contact in a contact list with multiple contacts. Are there any suggestions to have the formulas look through and modify multiple contacts?


(PS: The column "CL-TST refers to the Contacts column that has been set as a Test/Number column Type


The first is:

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


and the second is:

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

Answers

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭
    Options

    What are you trying to accomplish with this?

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Nicole V
    Nicole V ✭✭✭
    Options

    Hi, I am trying to extract emails from multiple contacts in a cell.

    I can only extract the first email address in a cell with multiple contacts in it.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide a screenshot with sample data?

  • Nicole V
    Nicole V ✭✭✭
    edited 06/08/23
    Options

    As you can see the formula:

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

    Is only returning the first contact's email address, but still retuning the contact name <email address> for the second and third contacts in the cell.

    Pr_TmCtc is set to Contacts in the column settings

    Tst user email, 2 is set to Number/Text in the column settings.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    How many email addresses would be the maximum amount in a single cell?

  • Nicole V
    Nicole V ✭✭✭
    Options

    prob abt ten? Max

  • Nicole V
    Nicole V ✭✭✭
    Options

    The max contacts a cell can take is 20 anyway

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 06/08/23
    Options

    My suggestion is to create separate columns for each then. Since there is the potential for that many, a single column formula would be absolutely monstrous.


    It looked like you have a column already that converted it from names to emails? Are you able to provide that screenshot again?

  • Nicole V
    Nicole V ✭✭✭
    Options

    In one cell:

    Just the first Contact out of three was converted to email.

    The other two stayed as Jane Doe <janedoe@email.com>, Joe Bloggs ,<joe.bloggs>@email.com

    That's my challenge - I have more than one contact that I am trying to convert to email in a cell. What if I Limited it to 3 contacts per cell? Is there a way to modify this:

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

    =SUBSTITUTE(MID([CL_Tst]@row, FIND("<", [CL_Tst]@row) + 2, LEN([CL_Tst]@row) - (FIND("<", [CL_Tst]@row) + 2)),'>"


    How do I put these two macros together, so the sheet looks for the first and the second records?


    Each one works on its own. I am even able to convert the second of the 3 contacts, but the first and third still stay the same.

  • Nicole V
    Nicole V ✭✭✭
    edited 06/13/23
    Options
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 06/13/23
    Options

    Hi @Nicole V

    If you are okay with using a lot of help columns, as suggested by Paul, here is a lazy way to extract emails.😅

    When you convert Contact List to text, it becomes a list of name/email combinations separated by "," such as nameA<emailA, nameB<emailB>, nameC<emailC>.

    Add a sufficient number of columns next to it. (Column5 to Column16, for example)

    Next to the text-converted Contact List cell, write a formula to replace the first combination (e.g., nameA<emailA>,) with SUBSTITUTE() to blank.

    =SUBSTITUTE([Contact List(Text)]@row, LEFT([Contact List(Text)]@row, FIND(","", [Contact List(Text)]@row)), "")

    (The actual formula is error handled.)

    Pull and copy the cell (Column 5) 's formula to the right. ( --> Pull formula)

    Then the name/email combination is removed from the top at each following left cell.


    With this format, we can use the same pattern MID function to extract the email part as the following;

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

    (The actual expression is error handled.)

    Write this formula in the cell in the next column (Column 11, for example) and pull and copy the cell's formula as before.

    By doing so, we can retrieve email addresses one after another.


    To connect them, for example, use the following formula

    =JOIN([Column11]@row:[Column15]@row, CHAR(10)) 


    Please take a look at the published sheet.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!