8

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?

Gareth
  • 18,809

4 Answers4

17

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
  • 6,288
  • 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.)

Arjan
  • 31,163
  • 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.

     –  Sep 26 '09 at 02: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
0

I love these ugly hacks. Looks like some dated info too. Get ready for 3rd party ref info to get obseleted even faster.. If you've got Office 365 business, check out Office Scripts (they finally added an alternative to VBA, but only on the otherwise limited cloud Excel):

https://www.howtoexcel.org/extract-hyperlink-url/

ctrl+f: Extract the Hyperlink URL with Office Scripts

TL;DR: Automate tab -> New Script

paste this typescript-like thingy:

    function main(workbook: ExcelScript.Workbook) {
    // Your code here
    // https://www.howtoexcel.org/extract-hyperlink-url/
    // TODO: extend/input a whole column.. since it balks at 
    // empty lines during selection

//Create a range object from selected range
  let selectedRange = workbook.getSelectedRange();
  //Create an array with the values in the selected range
  let selectedValues = selectedRange.getValues();
  //Get dimensions of selected range
  let rowHeight = selectedRange.getRowCount();
  let colWidth = selectedRange.getColumnCount();


//Loop through each item in the selected range
  for (let i = 0; i < rowHeight; i++) {
    for (let j = 0; j < colWidth; j++) {
      let currHyperlink = selectedRange.getCell(i, j).getHyperlink().address;
      selectedRange.getOffsetRange(0, colWidth).getCell(i, j).setValue(currHyperlink);
    };
  };
};

Yet another way to own the content you create. If you're doing a lot of this, I'd look into python's openpyxl, and stay away from these subscription text editors. This is ridiculous, but will keep you rolling today! I would buy a new MS office for this feature, but I am not going to pay subscription for them to change stuff all the time and make even more money on the same thing. Not being able to open your docs one day is bad enough, but this way you won't even be able to access the file. Microsoft seems like a real arm-twisting business partner, I would consider other options as they continue to tighten the screw and wring every dime out of an OS and Word processor from the 90's.

Mike
  • 9
  • 3
0

Or, if you cant afford subscription word processor office 365, another take on: https://superuser.com/a/62492/873385 /u/AdamV @AdamV 's ugly (awesome) hack

or could use other tips here to open all of the hyperlinks at once in a new browser window: https://www.howtoexcel.org/extract-hyperlink-url/

then use OpenList by Outpox in either Firefox or Chrome:https://addons.mozilla.org/en-US/firefox/addon/openlist/, https://chrome.google.com/webstore/detail/openlist/nkpjembldfckmdchbdiclhfedcngbgnl

Github here:

https://github.com/cdzombak/OpenList

(Outpox used to have one but I can't seem to find)

to generate copy-pasteable plaintext list for your application. Many ways to skin this cat!

Mike
  • 9
  • 3