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

Ask Your Question

extract hyperlink from a hyperlinked text to another cell

asked 2020-12-21 00:06:17 +0200

dexl gravatar image

updated 2020-12-21 00:09:36 +0200

image description

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 flag offensive close merge delete


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

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

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

Lupp gravatar imageLupp ( 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.

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

2 Answers

Sort by » oldest newest most voted

answered 2020-12-21 00:41:53 +0200

Lupp gravatar image

updated 2020-12-23 13:26:44 +0200

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.

Comments welcome.

edit flag offensive delete link 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.

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

answered 2020-12-22 19:13:38 +0200

dexl gravatar image

updated 2020-12-22 19:52:42 +0200

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

edit flag offensive delete link more


Please use the tool 'add comment' to give explanations concerning the answer or a comment by someone else. Edit your question to add information.
The answering tool should oinly be used for actual answers to the question. (This is not a forum, but a Q&A site.)

Lupp gravatar imageLupp ( 2020-12-22 22:44:22 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-12-21 00:06:17 +0200

Seen: 240 times

Last updated: Dec 23 '20