Find email addresses in column B and copy them to column C sequentially.

[MikeBerlan]

I have approximately 1800 agent data files in column B and need to sequentially copy the email addresses into column C. Note: the cell including the email address...E-Mail: jack@abcrealty.com[/B]...etc do not fall on every 8th row because each data file is different. Can someone help with an Excel Formula that can solve my problem?

Column B Column C
Example: John Smith jack@abcrealty.com
ABC Realty nancy@abcrealty.com
123 Adams Street
Jacksonville, FL 01234
Agent
Cell Phone: 123-45-6789
Home Phone: 123-45-6789
Website: www.sellingyourhome.com
E-mail: jack@abcrealty.com

Nancy Smith
ABC Realty
123 Eve Street
Jacksonville, FL 01234
Broker
Cell Phone: 123-45-6789
E-mail: nancy@abcrealty.com

 

[Peter_SSs]

If you are looking for a VBA solution, then give this macro a try...

.. or

Sub ExtractEmails()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:="E-mail*"
    .Offset(1).Copy Destination:=.Cells(1, 2)
    .AutoFilter
    .Offset(, 1).Replace What:="*:?", Replacement:="", LookAt:=xlPart
  End With
End Sub

 

[MikeBerlan]

Awesome!!! Thank you very much Peter!!! Have a great day!!!
Mike

 

[Peter_SSs]

Awesome!!! Thank you very much Peter!!! Have a great day!!!
Mike

You are welcome. Plenty of options.