I want two columns in an excel file. (very large with 35,000+ rows)
The column A has names of customers and for column B I want to
have a hyperlink to each customer via search by google.

Example: If column A of row 1 was "Boeing". Then column B of row 1
is http://www.google.com/search?hl=en&q...=Google+Search

I don't want the DIRECT company website (ie not www.boeing.com)
But the GOOGLE search of it.

SInce the list is over 35,000 rows it would be very time consuming to
search one by one in google and copy and paste the goolge search url.
Is there a automated way /via batch file writting that I can let one
excel file convert alll the names in column a into finished google
search urls in column B without having to do them one by one???


Thanks


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=535503

Can you set up column B as a concatentation of the google website and column
A -

i.e. B2 is
="http://www.google.com/search?hl=en&q=" & A2 & "&btnG=Google+Search"

you might then have to copy paste special back in to get the hyperlinks.

I tested with boeing and concorde and it seemed to work.

Regards

"bxc2739" wrote:


I want two columns in an excel file. (very large with 35,000+ rows)
The column A has names of customers and for column B I want to
have a hyperlink to each customer via search by google.

Example: If column A of row 1 was "Boeing". Then column B of row 1
is http://www.google.com/search?hl=en&q...=Google+Search

I don't want the DIRECT company website (ie not www.boeing.com)
But the GOOGLE search of it.

SInce the list is over 35,000 rows it would be very time consuming to
search one by one in google and copy and paste the goolge search url.
Is there a automated way /via batch file writting that I can let one
excel file convert alll the names in column a into finished google
search urls in column B without having to do them one by one???


Thanks


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=535503

Thanks the code is working.

One more question, I don't want it to do that for every row,
only those I highlighted in a particular color (ie Red)
but if I drag down the entire column is there a conditional formatting
that can
let it apply only when the cell is formated a certain color?

Thanks


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=535503

Is you red colour applied manually or via conditional formatting? If it
is conditionally formatted, then the formula which applies it could be
entered into another column to return some value (like "R"). Then you
could apply a filter to that column looking for "R" and then copy your
google search formula from above only into the visible rows.

Hope this helps.

Pete

Hi, Thanks for the response.

Actually I was hoping to do it with a macro if possible.

I have a long column 'L' with more than 35000+ rows.
About 20% of the entire column (~7000) fields/cells of L
are formatted violet (the interior color of the cell is violet)
The rest of the cells are white/nonformatted in color

However the color/noncolor is RANDOM and does not follow a pattern.

I would like to have a macro that tests if that cell is violet first,
then if yes, let it process the cell.

Here is what I am trying to do but the code does not work/compile
correctly

Option Explicit

SUB CHECKCOLOREXISTSTHENGETGOOGLEURL()


FOR EACH CELL IN COLUMN L FROM L1 TO
L31886


IF CELL.INTERIOR.COLORINDEX = 13 THEN
CELL.FORMULA =
=HYPERLINK(\"HTTP://WWW.GOOGLE.COM/SEARCH?HL=EN&Q=\" & C1 & \"+\" & H1
& \"&BTNG=GOOGLE+SEARCH\")

END IF
NEXT CELL






END SUB

thanks


--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=535503