LOOKUP Function

[spersad]

Hello all:

I have a coloumn of ~6000 rows. I copied this list from a PDF file, in order to extract emails. The emails are in the format:

Email: leneee.foo1@company.com


I am trying to write a function or macro that searches this column, finds the key word "email" or a pattern string "Email:" and copies that email address into a cell. My objective at the end is to have all emails in one list/vector/column that I can extract into a database.

I thought I could use the LOOKUP function for this. But I am not sure how to match it by keyword = email, or how to extract the entire email address/cell to another cell.

Is there a better way to do this vs. what am trying ?


Thanks in advance for the help.

Cheers,

Leneee

 

[vgreen]

Perhaps it would be worth tidying up the data first? Use Replace on the worksheet and replace all instances of "Email: " with nothing. That should leave the email addresses as a single string.

 

[spersad]

Hi vgreen:

Thank you. I tried your suggestion and now its a single email address. Do you know an easy way to extract it now that it is a string ? They are buried in a list with 6551 rows. According to the Find and Replace, there should be about 600 email address as part of the this list.

Thanks for the help.


Thanks,

Leneee

 

[DutchKevin]

Hi there
If you're doing this only once, try using a second column which searches for the @ symbol. If True or False you could lookup the cell value, and later Paste-Copy values onto a new sheet.

Kevin

 

[spersad]

Thank you Dutch. Do you know what the typical code for that would look like ? Am assuming I should use VBA ?


Thanks.

 

[DutchKevin]

Hi Spersad.
I assume data is in column A, then put in column B the following.
=IF(ISERROR(FIND("@";A1));"";A1)
Kevin

 

[spersad]

Hi DutchKevin:

Thanks for this. I tried the following and it worked:

IF(ISNUMBER(Search("@",A2)),"yes","no")

And that seem to work! Thanks so much!


Cheers,

Leneee

 

[DutchKevin]

No problem, glad to help.
Kevin