6

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?

Improve this question
14

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.

Improve this answer
2
  • Just what I was after! Novel solution. – cw84 Mar 18 '13 at 16:04
  • I do something similar for a particular report. I need to copy a certain range of data (which includes one column that has formulas that use HYPERLINK) to another worksheet and convert those links to cell-formatting-style links. First, I copy the whole range and Paste Values into the destination (the empty cells already have the desired formatting applied to them), then I go to Word and do a normal Paste. Then, on each link in Word, I right-click and choose "Copy Hyperlink", go to the already-pasted values in Excel and press CTRL-K, CTRL-V. That avoids the Word-table-formatting issues. – Dan Henderson Jan 19 '17 at 15:52
3

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

Improve this answer
2
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy