Extract multiple emails from one cell

[Thomas Anderson]

Hello,

I'm having trouble extracting emails from a single cell. I can have many emails in on cell in the format below and need to extract semi colon separated emails. Any assistance would be appreciated. Thank you!

This is what I have in one cell:

Jose Smith <JSmith@gmail.com>; Kelly Smith <Ksmith@gmail.com>; David Smith <DSmith@gmail.com>

This is what I need:

JSmith@gmail.com; Ksmith@gmail.com; DSmith@gmail.com

 

[JoeMo]

Assuming all your data is in the format you show, here's a lightly-tested UDF you can try. After you install it, use it just like any worksheet function as shown in the example.

Book2
	A	B
1	Jose Smith <JSmith@gmail.com>; Kelly Smith <Ksmith@gmail.com>; David Smith <DSmith@gmail.com>	JSmith@gmail.com;Ksmith@gmail.com;DSmith@gmail.com
Sheet4

Cell Formulas
Range	Formula
B1	B1	=EmailList(A1)

Function EmailList(S As String) As String
Dim FirstSplit, SecondSplit, i As Long, j As Long
FirstSplit = Split(S, ">;")
For i = LBound(FirstSplit) To UBound(FirstSplit)
    SecondSplit = Split(FirstSplit(i), "<")
    EmailList = EmailList & ";" & SecondSplit(1)
Next i
EmailList = Mid(EmailList, 2, Len(EmailList) - 2)
End Function

 

[Fluff]

Hi & welcome to MrExcel.
A formula option

+Fluff 1.xlsm
	A	B
1
2	Jose Smith <JSmith@gmail.com>; Kelly Smith <Ksmith@gmail.com>; David Smith <DSmith@gmail.com>	JSmith@gmail.com; Ksmith@gmail.com; DSmith@gmail.com
Master

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN("; ",,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,"<",">"),">","</m><m>")&"</m></k>","//m[contains(.,'@')]"))

 

[Thomas Anderson]

Assuming all your data is in the format you show, here's a lightly-tested UDF you can try. After you install it, use it just like any worksheet function as shown in the example.

Book2
	A	B
1	Jose Smith <JSmith@gmail.com>; Kelly Smith <Ksmith@gmail.com>; David Smith <DSmith@gmail.com>	JSmith@gmail.com;Ksmith@gmail.com;DSmith@gmail.com
Sheet4

Cell Formulas
Range	Formula
B1	B1	=EmailList(A1)

Function EmailList(S As String) As String
Dim FirstSplit, SecondSplit, i As Long, j As Long
FirstSplit = Split(S, ">;")
For i = LBound(FirstSplit) To UBound(FirstSplit)
    SecondSplit = Split(FirstSplit(i), "<")
    EmailList = EmailList & ";" & SecondSplit(1)
Next i
EmailList = Mid(EmailList, 2, Len(EmailList) - 2)
End Function

I just learned how to setup UDF. This works! Thank you Joe!

 

[Thomas Anderson]

Hi & welcome to MrExcel.
A formula option

+Fluff 1.xlsm
	A	B
1
2	Jose Smith <JSmith@gmail.com>; Kelly Smith <Ksmith@gmail.com>; David Smith <DSmith@gmail.com>	JSmith@gmail.com; Ksmith@gmail.com; DSmith@gmail.com
Master

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN("; ",,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,"<",">"),">","</m><m>")&"</m></k>","//m[contains(.,'@')]"))

This was easier to implement than UDF and this works. Thank you Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JoeMo]

I just learned how to setup UDF. This works! Thank you Joe!

You are welcome - thanks for the reply.