14

I have a LibreOffice 3 spreadsheet (on Ubuntu 11.04) with a column of hundreds of hyperlinks which simply display as 'Link'.

I need to convert these to just plain text, or at worse hyperlinks which display the hyperlink rather than the text 'Link'. Is there a function which will do this, or perhaps a method that will do it across the entire spreadsheet?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Christian Mayne
  • 1,709
  • 7
  • 26
  • 42

3 Answers3

19

You can use a macro.

The only one I've tested is this one.

If you haven't had experience with macros, here are the steps involved:

  1. Copy the macro provided at that link.
  2. Press ALT + F11 to open your macros organizer.
  3. Under "My Macros" select "Module 1" and click on "Edit"
  4. Paste the macro code that you copied.
  5. Save and close your macro

Now, you have access to a macro titled "CELL_URL" which you can use as follows:

Assuming that your links start in "CELL A1" on "SHEET 1", I recommend you go to a new sheet, and in the first cell, enter: =CELL_URL("SHEET 1",ROW(),COLUMN()). Then, you can drag that formula according to the dimensions of your data.

Or, another example, assuming that you have a single column of 20 links, starting from "CELL B5" on "SHEET 1", and that you want the URL to be in the column next to it (thus, starting from "CELL C5"), in "CELL C5", enter the formula as follows: =CELL_URL("SHEET 1", ROW(), COLUMN()-1). Note the -1. That tells the function to extract the URL from the cell from one column less than the current position.Then, drag the formula from "CELL C5" to "CELL C24", where your links end.

Update:

The URL doesn't seem to be resolving. Fortunately, there's the WayBack Machine: http://web.archive.org/web/20120713222701/http://www.oooforum.org/forum/viewtopic.phtml?t=32909

I've also posted the macro as a Gist: https://gist.github.com/mrdwab/14c9e81dfbc867351bd3

As can be noted in the comments, you may need to change "SHEET 1" to "Sheet1" when using the CELL_URL() function.

A5C1D2H2I1M1N2O1R2T1
  • 190,393
  • 28
  • 405
  • 485
  • Thanks very much for this - I will test shortly and post my results – Christian Mayne Apr 27 '12 at 16:40
  • Well reminded! Still on my To Do list, unfortunately, but you have prompted me to get on with it. Maybe a job for this weekend. I will feedback as soon as done – Christian Mayne May 11 '12 at 11:29
  • 1
    I tried it out just now, and it worked like a charm. It was "Sheet1" without a space for me--just look closely at the sheet tab when you use it. – cayhorstmann Aug 23 '12 at 15:39
  • Hm - the link you're pointing to isn't resolving for me - does anyone have a copy of the macro in question? – John Fiala Jul 24 '14 at 20:15
  • CELL_URL has the this too, [quoting from here](http://web.archive.org/web/20120713222701/http://www.oooforum.org/forum/viewtopic.phtml?t=32909):"Optional parameter allows to extraction of an URL from another one than the first hyperlink (default=1 for first hyperlink)." By using that you can navigate many links present on a cell, and select which one you need. – Santropedro May 03 '20 at 00:44
5

Here is another way, using Unix command line tools - and bypassing Calc altogether.

First of all, unpack the .ods you have as a zip; example:

$ cd /tmp
$ mkdir my_ods_unpack
$ cd my_ods_unpack
$ unzip /path/to/my.ods

Once unzipped, you can look in ./content.xml, and realize all the URLs are enclosed in double quotes "; thus we can write the following grep line:

$ grep -ro 'http[^"]*' .
./meta.xml:http://www.w3.org/1999/xlink
./meta.xml:http://purl.org/dc/elements/1.1/
./meta.xml:http://openoffice.org/2004/office
...

... and once it gets to ./content.xml, you should start seeing the URLs that have been put in the document.

sdbbs
  • 4,270
  • 5
  • 32
  • 87
0

If you want to get the URL embedded to a cell in office calc you can access it as follow :

cell1.TextFields.getByIndex(0).URL

cell1 is the cell object obtained using UNO.

Niharranjan Pradhan
  • 131
  • 1
  • 3