SOLVED

Making an email list

%3CLINGO-SUB%20id%3D%22lingo-sub-1607812%22%20slang%3D%22en-US%22%3EMaking%20an%20email%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1607812%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20contact%20info%20in%20one%20column.%20It%20includes%20names%2C%20phone%20numbers%20and%20email%20addresses%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20put%20all%20of%20those%20email%20addresses%20only%20into%20one%20list%3F%3C%2FP%3E%3CP%3E(Sorry%20if%20this%20is%20basic%20knowledge.%20I%20don't%20use%20sheets%20often.)%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ELT%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1607821%22%20slang%3D%22en-US%22%3ERe%3A%20Making%20an%20email%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1607821%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F768950%22%20target%3D%22_blank%22%3E%40ltwestdistrict%3C%2FA%3E%26nbsp%3B%20Did%20you%20try%20highlighting%20the%20column%20and%20Clicking%20Data-%26gt%3BText%20to%20Columns%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608075%22%20slang%3D%22en-US%22%3ERe%3A%20Making%20an%20email%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608075%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20I%20did%20not.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20nervous%20about%20losing%20data%20or%20messing%20up%20my%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608079%22%20slang%3D%22en-US%22%3ERe%3A%20Making%20an%20email%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608079%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F768950%22%20target%3D%22_blank%22%3E%40ltwestdistrict%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESave%20a%20copy%20first%20then.%20You%20can%20mess%20it%20up%20without%20worry.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608108%22%20slang%3D%22en-US%22%3ERe%3A%20Making%20an%20email%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608108%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20that%20made%20a%20mess%20of%20things.%3C%2FP%3E%3CP%3EI%20copied%20the%20column%20to%20a%20new%20sheet%20thankfully.%3C%2FP%3E%3CP%3EIt%20helped%20some.%20I%20will%20keep%20trying%20to%20split%20the%20text%20into%20other%20columns.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608263%22%20slang%3D%22en-US%22%3ERe%3A%20Making%20an%20email%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608263%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F768950%22%20target%3D%22_blank%22%3E%40ltwestdistrict%3C%2FA%3E%26nbsp%3BWell%20I'm%20sorry%20that%20made%20a%20mess%20of%20things%2C%20it%20usually%20does%20a%20pretty%20good%20job.%26nbsp%3B%20You%20can%20also%20create%20a%20custom%20formula%20to%20split%20that%20data%20out%20but%20without%20knowing%20the%20content%20of%20the%20list%20it%20is%20hard%20to%20know%20what%20'rules'%20can%20be%20used%20to%20reliably%20pull%20the%20e-mail%20out.%26nbsp%3B%20I%20created%20the%20following%20formula%20with%20what%20I%20believe%20has%20only%202%20assumptions%3A%3C%2FP%3E%3CP%3Ethe%26nbsp%3B%40%20symbol%20is%20ONLY%20present%20in%20the%20e-mail%3C%2FP%3E%3CP%3Ethe%20e-mail%20is%20preceded%20and%20succeeded%20by%20a%20space%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A2%2CSEARCH(%22%40%22%2CA2))%2C%22%20%22%2CREPT(%22%20%22%2CLEN(A2)))%2CLEN(A2)))%26amp%3BTRIM(LEFT(SUBSTITUTE(RIGHT(A2%2CLEN(A2)-SEARCH(%22%40%22%2CA2))%2C%22%20%22%2CREPT(%22%20%22%2CLEN(A2)))%2CLEN(A2)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ethis%20formula%20is%20targeting%20A2%20and%20would%20need%20to%20be%20adjusted%20to%20the%20first%20cell%20you%20want%20to%20point%20at%20and%20drag%20down.%26nbsp%3B%20If%20your%20version%20of%20excel%20has%20dynamic%20arrays%20you%20can%20use%20this%20version%20that%20points%20at%20the%20entire%20column%20of%20A%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(A%3AA%2CSEARCH(%22%40%22%2CA%3AA))%2C%22%20%22%2CREPT(%22%20%22%2CLEN(A%3AA)))%2CLEN(A%3AA)))%26amp%3BTRIM(LEFT(SUBSTITUTE(RIGHT(A%3AA%2CLEN(A%3AA)-SEARCH(%22%40%22%2CA%3AA))%2C%22%20%22%2CREPT(%22%20%22%2CLEN(A%3AA)))%2CLEN(A%3AA)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20hope%20one%20of%20these%20work%20and%20if%20so%20maybe%20consider%20marking%20this%20as%20the%20'best%20answer'%20instead%20of%20your%20own%20response%20about%20how%20things%20didn't%20work%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

5 Replies

@ltwestdistrict  Did you try highlighting the column and Clicking Data->Text to Columns?

@mtarler 

 

No I did not. 

I get nervous about losing data or messing up my sheets.

 

@ltwestdistrict 

Save a copy first then. You can mess it up without worry.

Best Response confirmed by ltwestdistrict (New Contributor)
Solution

@mtarler 

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