Aug 24 2020 05:45 AM
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
Aug 24 2020 05:53 AM
@ltwestdistrict Did you try highlighting the column and Clicking Data->Text to Columns?
Aug 24 2020 07:11 AM
Aug 24 2020 07:14 AM
Save a copy first then. You can mess it up without worry.
Aug 24 2020 07:24 AM
SolutionWell, 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!
Aug 24 2020 08:09 AM
@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 :)
Aug 24 2020 07:24 AM
SolutionWell, 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!