Forum Discussion
Excel
I have a client list in excel that contains name, address, and phone. On some records, the email address is in the address. Across columns are various other items (invoice amounts, dates, etc.). I need to extract the email address from column A into a separate column. Is there a way to do this without going to each individual client record?
Hi
Address Line 1 Address Line 2 Address Line 3 Email Address Line 1 1200 SPUR ST #300 MARKET, MO 62206 1200 SPUR ST 4522 Main Dr Nowhere, TN 37544 4522 Main Dr MYEMAIL@YAHOO.COM MYEMAIL@YAHOO.COM youremail@gmail.com youremail@gmail.com 123 Any Street #401 Anytown, TX 11111 123 Any Street Talktome@rocket.net Talktome@rocket.net Columns F:J
Formula in I:
=IF(COUNTIFS(F2,"*@*"),F2,"")
Formula in J:
=SUBSTITUTE(F2,I2,"")
5 Replies
Do you mean email address is within another text in same cells or that's only email address in the cell instead of post address? Better if you give a sample with few records of how it looks like (without sensitive information).
I provided an example. Is it not possible to do this?
Hi
Address Line 1 Address Line 2 Address Line 3 Email Address Line 1 1200 SPUR ST #300 MARKET, MO 62206 1200 SPUR ST 4522 Main Dr Nowhere, TN 37544 4522 Main Dr MYEMAIL@YAHOO.COM MYEMAIL@YAHOO.COM youremail@gmail.com youremail@gmail.com 123 Any Street #401 Anytown, TX 11111 123 Any Street Talktome@rocket.net Talktome@rocket.net Columns F:J
Formula in I:
=IF(COUNTIFS(F2,"*@*"),F2,"")
Formula in J:
=SUBSTITUTE(F2,I2,"")
The email address is in Column F of the row which contains client information. But in some records, Column F is the street address. Need to just move the email addresses to a column of their own in the same client record row. See attached.
