Extracting emails (Multiple) from a contact list

Nicole V

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)

Kleerfyre

What are you trying to accomplish with this?

Nicole V

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

Are you able to provide a screenshot with sample data?

Nicole V

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

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

Nicole V

prob abt ten? Max

Nicole V

The max contacts a cell can take is 20 anyway

Paul Newcome

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

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

jmyzk_cloudsmart_jp

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.

https://app.smartsheet.com/b/publish?EQBCT=bdec9a3501184784b3960960aa5ecb54