'How to extract email & name from multi-line strings in cells in excel
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.
Solution 1:[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),"")
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |