Need to create 1,000's of links quickly

[Robb]

Is it possible to automatically add links to each entry in a given column?

i.e. In column A, I need each word/set of words to be hyperlinked without me having to manually do it myself.

Something like this:

Column A
Smith, Joe------> http://www.mysite.com/redirect.cgi?redirect=Smith_Joe
Thomas, Henry --> http://www.mysite.com/redirect.cgi?r...t=Thomas_Henry

(I also need the underscore between the two names in the hyperlinks "_".)

Is this possible?

Thank you

[Paul]

Assuming Column A has your data ("Smith, Joe"; "Johnson, Bob"; etc.), in B1 copy and paste the following formula:

=HYPERLINK("http://www.mysite.com/redirect.cgi?redirect="&(SUBSTITUTE(A1,", ","_")))

Then fill that down for all your rows of names.

[Robb]

Assuming Column A has your data ("Smith, Joe"; "Johnson, Bob"; etc.), in B1 copy and paste the following formula:

=HYPERLINK("http://www.mysite.com/redirect.cgi?redirect="&(SUBSTITUTE(A1,", ","_")))

Then fill that down for all your rows of names.

I need to actually make the names in Column A to be hyperlinks.

i.e. I need it to look like this:

Smith, Joe

[Paul]

Ok, then try this:

Create a temporary helper row after column A and use my original formula with a slight modification in B1 downward:

=HYPERLINK("http://www.mysite.com/redirect.cgi?redirect="&(SUBSTITUTE(A1,", ","_")),A1)

By adding the last ",A1" it creates the hyperlink but using the 'friendly name' as seen in cell A1. Fill down to row 65,536 if necessary. Then you can copy column B and PasteSpecial->Values into Column A. Delete column B and you should be all set. This should take 30 seconds or less.

If you want a macro to do this without taking the 30 seconds to use the formula above, then someone else will need to step in since I'm not a vba expert of any kind.

[Robb]

Ok, then try this:

Create a temporary helper row after column A and use my original formula with a slight modification in B1 downward:

=HYPERLINK("http://www.mysite.com/redirect.cgi?redirect="&(SUBSTITUTE(A1,", ","_")),A1)

By adding the last ",A1" it creates the hyperlink but using the 'friendly name' as seen in cell A1. Fill down to row 65,536 if necessary. Then you can copy column B and PasteSpecial->Values into Column A. Delete column B and you should be all set. This should take 30 seconds or less.

If you want a macro to do this without taking the 30 seconds to use the formula above, then someone else will need to step in since I'm not a vba expert of any kind.

Looks good. Thanks! One last thing...

I have some names listed like this:

Smith, Joe Bob

but I want it to get linked with the ending looking like:

Smith_Joe_Bob

The way you showed me does the following:

Smith_Joe Bob

Any way around turning SPACES into UNDERSCORES?

[Paul]

Sure, throw in an extra SUBSTITUTE function on top of the other one, like so:

=HYPERLINK("http://www.mysite.com/redirect.cgi?redirect="&(SUBSTITUTE(SUBSTITUTE(A2,", ","_")," ","_")),A2)

This will first replace the comma+space (", ") with an underscore, then it will replace any remaining spaces with underscores.