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