5
0
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?
10
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.
AdamV
Posted 2009-09-26T01:46:22.883
Reputation: 5 011
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.)
Arjan
Posted 2009-09-26T01:46:22.883
Reputation: 29 084
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. – None – 2009-09-26T02:38:12.647
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 – 2009-09-26T10:36:39.637
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 – 2017-01-19T15:52:36.540
Just what I was after! Novel solution. – cw84 – 2013-03-18T16:04:28.847