Forum Discussion
Making an email list
I have a list of contact info in one column. It includes names, phone numbers and email addresses
Is there a way to put all of those email addresses only into one list?
(Sorry if this is basic knowledge. I don't use sheets often.)
Thanks
LT
Well, that made a mess of things.
I copied the column to a new sheet thankfully.
It helped some. I will keep trying to split the text into other columns.
Thanks!
5 Replies
ltwestdistrict Did you try highlighting the column and Clicking Data->Text to Columns?
Well, that made a mess of things.
I copied the column to a new sheet thankfully.
It helped some. I will keep trying to split the text into other columns.
Thanks!
ltwestdistrict Well I'm sorry that made a mess of things, it usually does a pretty good job. You can also create a custom formula to split that data out but without knowing the content of the list it is hard to know what 'rules' can be used to reliably pull the e-mail out. I created the following formula with what I believe has only 2 assumptions:
the @ symbol is ONLY present in the e-mail
the e-mail is preceded and succeeded by a space
=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A2,SEARCH("@",A2))," ",REPT(" ",LEN(A2))),LEN(A2)))&TRIM(LEFT(SUBSTITUTE(RIGHT(A2,LEN(A2)-SEARCH("@",A2))," ",REPT(" ",LEN(A2))),LEN(A2))),"")this formula is targeting A2 and would need to be adjusted to the first cell you want to point at and drag down. If your version of excel has dynamic arrays you can use this version that points at the entire column of A:
=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A:A,SEARCH("@",A:A))," ",REPT(" ",LEN(A:A))),LEN(A:A)))&TRIM(LEFT(SUBSTITUTE(RIGHT(A:A,LEN(A:A)-SEARCH("@",A:A))," ",REPT(" ",LEN(A:A))),LEN(A:A))),"")I hope one of these work and if so maybe consider marking this as the 'best answer' instead of your own response about how things didn't work 🙂
Save a copy first then. You can mess it up without worry.
