We will be migrating from Ask to Discourse on the first week of August, read the details here

Hello, I copied a hyperlinked text and pasted it into Calc (B3). Now, I would like to copy it into a new cell (C3), give it a "nicer" name and make it clickable, but as soon as I do that (with =HYPERLINK(B3,"PLAY")), the url hyperlink gets cleared and it points to arbitrary Flat14:15NOW PLAYING, which is not a link. How can I do that? Thank you for your help.

edit retag close merge delete

I think that B3 content must be the URL text. C3 it's OK.

( 2020-12-21 00:29:02 +0200 )edit

You should consider that a cell's text may contain many linked portions.

( 2020-12-21 00:36:44 +0200 )edit

Function HYPERLINK() is not about extracting the URL from a cell, but is about to create an hyperlink from the textual content.

( 2020-12-21 11:41:25 +0200 )edit

Sort by » oldest newest most voted

You may apply the user function I supplied in this post to a different forum: https://forum.openoffice.org/en/forum...
Of course you may simplify the code for specialized purposes. However, html offers a well-proven syntax to give hyperlinks.

=== Edit 2020-12-22 about 21:40 UTC===
I don't know an on-board tool of Calc extracting URLs from linked text, but I took the time to simplify the old user code I had pointed to, and to make it work similar to the tool the questioner pointed to, respecting the original data, and returning the results to a different range.

There is one problem I already mentioned in my first comment on the question: A single cell's text may contain more than one hyperlink. The alien tool seems to ignore the fact. It remains a fact nonetheless, and I don't like to write code failing the next time.

There are two ways: Only return the FIRST url, and ignore the rest, or use a synatx allowing to return all the linked URLs in one cell. The well established syntax is html, and that was what I originally chose.

The code contained in the attachment will allow you to choose the way appropriate for you.

more

To use the code contained in the attachment to the answer on a regular basis, it should be copied to a module of the Standard library in My Macros & Dialogs for Basic in the local LibreOffice.
Please note that the functions use a way to get passed the range to work on which only is applicable if Option VBAsupport 1 is set.
The Sub only works with a single range in the given form, but can easily be enhanced to also accept multi-selections.

( 2020-12-23 13:34:47 +0200 )edit

Hello, Thank you all for your help. I'm not too sure I explained what I want to achieve clearly enough. The solutions you proposed don't give me the answer (Lupp, I copied the macro, but I'm not sure I know how to execute it on my links specifically, but thank you nevertheless).

It seems that if I have a plain text (Click me) with a plain hyperlink (www.abc.com), I can combine the two in another cell. However, once I have copied a text that is already hyperlinked, I cannot combine it with a text from another cell or extract the hyperlink.

This is what I am trying to achieve, but the procedure is for Microsoft Excel, can this be done in Calc? https://www.extendoffice.com/product/kutools-for-excel/excel-convert-hyperlinks.html

more