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

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 
[email protected]

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.

CodePudding user response：

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),"")