How to extract email & name from multi-line strings in cells in excel

Question

Example of cells below. As you can see some cells have more info including emails, middle initials or names, but some don't. They're all in the same column.

Cell 1

Smith, James
#129432
123 N. Street Road
 Libertyville, IL, 60048
(810) 955-9721 
claudie.predov@hotmail.com

Cell 2

Evette Tar Rudnick
#7928253
1308 Stutler Lane
Tidioute, PA, 16351

Cell 3

David Ponce C
#1234567
2855 Retreat Avenue
 Frenchboro, ME, 04635
(313) 204-6364

Any help is appreciated. Thank you.

Answer 1

You may try in this way,

• Formula used in cell C1

=FILTERXML("<a><b>"&SUBSTITUTE(A1,CHAR(10),"</b><b>")&"</b></a>","//b[1]")

• Formula used in cell D1

=IF(ISNUMBER(FIND("@",A1)),FILTERXML("<a><b>"&SUBSTITUTE(A1,CHAR(10),"</b><b>")&"</b></a>","//b[last()]"),"")

Note: I have assumed the strings are segregated within a cell by line breaks hence why CHAR(10) has been used.

---

Or, if you have access to the O365 & currently in Insider's Beta Channel Version then you may try using TEXTBEFORE() & TEXTSPLIT() Functions as well,

• Formula used in cell E1

=TEXTBEFORE(A1,CHAR(10),1)

• Formula used in cell F1

=IFERROR(INDEX(TEXTSPLIT(A1,,CHAR(10)),6),"")