Take the 2-minute tour ×

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


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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
Just what I was after! Novel solution. –  cw84 Mar 18 '13 at 16:04
add comment

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.)

share|improve this answer
Ok but if say I write some text in a cell and right click --> add hyperlink and then add a link that way. The cell retains the text in the formula bar and adds the link as a kind of meta data. Right now the cells i have linked contain this =HYPERLINK(C2,A2) in the formula bar. –  Pete Sep 26 '09 at 2:38
Can you also perform that right-click trick on part of the text? (Just like you could make part of the text appear in bold, etcetera.) And what happens when using Paste Values on cells on which you've used that right-click option? –  Arjan Sep 26 '09 at 10:36
add comment

Your Answer

