software

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 quot;Boeingquot;. Then column B of row 1
is www.google.com/search?hl=enamp;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: www.excelforum.com/member.php...oamp;userid=32538
View this thread: www.excelforum.com/showthread...hreadid=535503Can you set up column B as a concatentation of the google website and column
A -

i.e. B2 is
=quot;www.google.com/search?hl=enamp;q=quot; amp; A2 amp; quot;amp;btnG=Google Searchquot;

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

quot;bxc2739quot; wrote:

gt;
gt; I want two columns in an excel file. (very large with 35,000 rows)
gt; The column A has names of customers and for column B I want to
gt; have a hyperlink to each customer via search by google.
gt;
gt; Example: If column A of row 1 was quot;Boeingquot;. Then column B of row 1
gt; is www.google.com/search?hl=enamp;q...=Google Search
gt;
gt; I don't want the DIRECT company website (ie not www.boeing.com)
gt; But the GOOGLE search of it.
gt;
gt; SInce the list is over 35,000 rows it would be very time consuming to
gt; search one by one in google and copy and paste the goolge search url.
gt; Is there a automated way /via batch file writting that I can let one
gt; excel file convert alll the names in column a into finished google
gt; search urls in column B without having to do them one by one???
gt;
gt;
gt; Thanks
gt;
gt;
gt; --
gt; bxc2739
gt; ------------------------------------------------------------------------
gt; bxc2739's Profile: www.excelforum.com/member.php...oamp;userid=32538
gt; View this thread: www.excelforum.com/showthread...hreadid=535503
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=32538
View this thread: www.excelforum.com/showthread...hreadid=535503Is 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 quot;Rquot;). Then you
could apply a filter to that column looking for quot;Rquot; 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
L31886IF CELL.INTERIOR.COLORINDEX = 13 THEN
CELL.FORMULA =
=HYPERLINK(\quot;HTTP://WWW.GOOGLE.COM/SEARCH?HL=ENamp;Q=\quot; amp; C1 amp; \quot; \quot; amp; H1
amp; \quot;amp;BTNG=GOOGLE SEARCH\quot;)

END IF
NEXT CELL
END SUB

thanks--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: www.excelforum.com/member.php...oamp;userid=32538
View this thread: www.excelforum.com/showthread...hreadid=535503