Ask Your Question
0

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

Comments

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
0

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.
C:\fakepath\ask283622extractURLdemo_1.ods

Comments welcome.

edit flag offensive delete link more

Comments

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
0

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

Comments

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

Stats

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

Seen: 145 times

Last updated: Dec 23 '20