How do I separate a long string of emails with semi-colon?

[The_ELK]

Hello,

I have 1 Excel Cel (A1) with a long string of people email addresses without a bloody space. It's hard for me to insert this in Outlook without Outlook bombing out.

The Cell is like this as an example:

Yourname@email.comJAckTest@hotmail.comCNN@email.comNBA@cloud.comReception@compnay.com

<tbody>
</tbody>

What is the best formula to place a semi-colon between so that I can dump it in Outlook?

 

[sandy666]

Yourname@email.comJAckTest@hotmail.comCNN@email.comNBA@cloud.comReception@compnay.com
Yourname@email.com;JAckTest@hotmail.com;CNN@email.com;NBA@cloud.com;Reception@compnay.com;		=SUBSTITUTE(A1,".com",".com;")

but I think this is not representative example

you can use nested SUBSTITUTE again for .org .net etc

 

[JoeMo]

In the examples below, cell B1 is a formula that deals only with .com email domains. You can nest it similar formulas if your list contains other domains like .org, .gov, .edu, .....

Cell B2 is a UDF that posted below the examples. For me it's an easier way to deal with multiple domains. If you install it then it can be used just like a worksheet function as in cell B2.

Excel Workbook
	A	B
1	Yourname@email.comJAckTest@hotmail.comCNN@email.comNBA@cloud.comReception@compnay.com	Yourname@email.com;JAckTest@hotmail.com;CNN@email.com;NBA@cloud.com;Reception@compnay.com
2	Yourname@email.comJAckTest@hotmail.govCNN@email.orgNBA@cloud.comReception@compnay.edu	Yourname@email.com;JAckTest@hotmail.gov;CNN@email.org;NBA@cloud.com;Reception@compnay.edu
Sheet3

UDF Alternative

Function EmailDelim(S As String, Delim As String) As String
Dim Domain As Variant, i As Long
Domain = Array(".com", ".org", ".edu", ".gov") 'add other domain as required
For i = LBound(Domain) To UBound(Domain)
    S = Replace(S, Domain(i), Domain(i) & Delim)
Next i
EmailDelim = Left(S, Len(S) - 1)
End Function

 

[The_ELK]

but I think this is not representative example
you can use nested SUBSTITUTE again for .org .net etc

perfect, works like a charm

luckily, all my domains end with [.com] hahaha, I wouldnt bother if I had a mixture!!

 

[sandy666]

You are welcome

Have a nice day

 

[Lazzg]

Hi,

Have you tried doing a find and replace. Find com and replace with com;

Simple but should do the job.

Cheers