Using Paste Special > Values with the HYPERLINK function in Excel

I have a long list of data in Excel 2007. Using the Hyperlink function:

=HYPERLINK(C2,A2)

I've added individual links to them all. Now I want to get rid of the function and just leave the data with the link.

If I do Paste Special > Values, it does the job but the link will be removed.

How can I paste the values and keep the hyperlink?

Solution 1:

How about a paste out to Word? (and then copy it there and paste back into Excel if you really need it back in Excel).

This retains both the link and the title, and is clickable.

No paste special required, although depending on your settings you may get some naff formatting inherited from the the default tabel format in Word. You can get round this using the paste options in Word to keep the source formatting.

Solution 2:

Errrr, the function is the link. There's no such thing as a clickable link without that function. (Consider this: which value would you expect when pasting something that is both a clickable link and a title? C2 or A2?)

You could replace the function with just C2. After that, using Paste Values will give you a non-clickable link. (But: you would already have that very same non-clickable link in C2 itself, so in the end that's useless.)

Or, if you expect some HTML magic: use something like the following, which is not clickable in Excel, but may be helpful when you use the result elsewhere:

= "<a href='" & C2 & "'>" & A2 & "</a>"

(Where & is the short notation for function CONCATENATE.)