Excel Function

[jrortiz_2]

Hello guys,

Not really sure what sort of formula I can use for my data. Is there any way I can separate each email addresses listed in a cell?
Example:

A1: holger@kodal.comtotto@kodal.netsoren@kodal.ukfrede@kodal.dkrie@kodal.netkodal@kodal.netfrede@kodla.se

Normally each email address ends in .se .uk .no. and many many more. I want to know how can I separate them by placing a formula in B1?

Thanks

 

[wideboydixon]

Are there any characters between the email addresses? If you don't have spaces or something else then this is a problem - you'll need an exhaustive list of endings and you'll need to use a macro.

WBD

 

[jrortiz_2]

no other characters. not even space Thanks

 

[wideboydixon]

Here's what I tried:

Public Sub SplitEmailAddresses()

Dim endings As Variant
Dim allEmails As String
Dim separateMails As Variant
Dim index As Long

' Change to list all possible endings
endings = Array(".com", ".co.uk", ".se", ".no")

' Retrieve the concatenated emails
allEmails = Range("A1").Value

' Process each ending
For index = 0 To UBound(endings)
    allEmails = Replace(allEmails, endings(index), endings(index) & "|", 1, -1, vbTextCompare)
Next index

' Split out the results
If Right$(allEmails, 1) <> "|" Then allEmails = allEmails & "|"
separateMails = Split(allEmails, "|")

' Populate the answers
For index = 0 To UBound(separateMails) - 1
    Cells(1, index + 2).Value = separateMails(index)
Next

End Sub

Here's the result:

Book1
	A	B	C	D	E
1	a@b.comd@abc.co.ukmonkeys@apes.seoslo@norway.no	a@b.com	d@abc.co.uk	monkeys@apes.se	oslo@norway.no
Sheet1

WBD