Moving data from worksheet to columns

[nobleprince]

Hi,

I have a new/different requirement.

Could you please assist with updating the code so that the hyperlinks are captured from worksheet "Company names" and placed into column D within "Sheet1".

The macro should check for any words after the "at" or "@" word/sign (within column E), and if there are any exact words found after the "at" or "@" then the macro should take the URL from within worksheet "Company names" and put them into column D. If no words are found after the "at" or "@" sign then the cell should be marked as "-".

I have attached the spreadsheet with BEFORE and AFTER.

I look forward to your assistance.

Thank you,
Ash

[Marcol]

Given that you are using 2007
In D3

Please Login or Register  to view this content.

Drag/Fill Down

2003 will require a helper column, see the attached.

All versions could use this UDF in a standard module

Please Login or Register  to view this content.

Enter in D3

Please Login or Register  to view this content.

Drag/Fill Down

[nobleprince]

Hi Marcol,

I have Excel version 2007. I am fairly novice to the world of macros. I'm not able to run the macro/code for the file you have attached. Could you please create a button which I can press and it should arrange the data. Please note, that I have over 1000 records and/or company names and I should be able to run the macro for 1000+ records and/or company names. Also, I am not sure why two excel spreadsheets open when I open the attachment? Only one file should open.

Thank you.
Ash

[Marcol]

1/. I don't fhink that two workbooks are opening, is it not just one workbook showing in two windows?

2/. Does the formula I gave you for 2007 work as you need? This should be your preferred option.

3/. The code is for a User Defined Function, no need for buttons, just put =GetURL(E3) in D3 then drag the fill handle down to where your data ends.

[nobleprince]

It works great. Thank you very much Marcol. Keep up the good work.