Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.