Extract 2 email addresses from a string of text

[test_man2]

Greetings!

Back in 2006, fairwinds posted a solution to extract an email address from a string of text:
=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",20)),FIND("@",SUBSTITUTE(" "&A1," ",REPT(" ",20)))-20,40))

What if that string of text contains two email addresses? Such as - "Update abcdefg@gmail.com to tuvwxyz@att.net"

My guess is that the best way would be to create a new formula in column C (the above formula being in column B). What would that formula be?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1
2	Update abcdefg@gmail.com to tuvwxyz@att.net	abcdefg@gmail.com	tuvwxyz@att.net
3	Update tuvwxyz@att.net	tuvwxyz@att.net
Lists

Cell Formulas
Range	Formula
B2:C2,B3	B2	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[contains(.,'@')]"))
Dynamic array formulas.

 

[test_man2]

Wow, that's great! If only you could clearly explain to me step-by-step how this works! I've never seen a formula extend results across more than one cell! Alas, I know little to nothing about dynamic array formulas, and I've never seen a formula with FILTERXML in it, so any explanation you provide would probably just go way over my head! My line of thinking was perhaps somehow using RIGHT in a formula in column C, which would only work if the address were at the end of the string, but yours works no matter where they are. Thanks for the solution!

 

[Fluff]

You're welcome & thanks for the feedback.