Formula to extract emails - Need Help

[sunnyujjawal]

Hi we have 90,000+ emails in excel with some text in this format:


abc123@rediffmail.com:61c090a3b0f
boosterad@hotmail.com:951c8ee0
sdhs44@yahoo.com:a981d71b7c935


How to extract emails from this.

 

[JackDanIce]

Try:

=LEFT(A1,FIND(A1,":")-1)

 

[Rick Rothstein]

Hi we have 90,000+ emails in excel with some text in this format:

abc123@rediffmail.com:61c090a3b0f
boosterad@hotmail.com:951c8ee0
sdhs44@yahoo.com:a981d71b7c935

How to extract emails from this.

First off, I am assuming your are showing three rows of data in Column A. If you do not want to retain the original data, you can convert those text values in email addresses directly within their cells by using Excel's Replace dialog box. The simplest way to bring up the Replace dialog box is to press CTRL+H (otherwise select the Home tab on the Ribbon, click the "Find & Select" button on the "Editing" panel and click the "Replace" item on the popup menu that appears), then put :* in the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button and make sure the "Match entire cell contents" checkbox is not checked, then click the "Replace All" button.

 

[sunnyujjawal]

Not working .. getting yellow sign error..

 

[AlKey]

Try

[B]=LEFT(A1,FIND(":",A1)-1)[/B]

Excel 2016 (Windows) 32 bit
	A	B
1	abc123@rediffmail.com:61c090a3b0f	abc123@rediffmail.com
2	boosterad@hotmail.com:951c8ee0	boosterad@hotmail.com
3	sdhs44@yahoo.com:a981d71b7c935	sdhs44@yahoo.com
Sheet1