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.
I think that B3 content must be the URL text. C3 it’s OK.
You should consider that a cell’s text may contain many linked portions.
Function HYPERLINK() is not about extracting the URL from a cell, but is about to create an hyperlink from the textual content.
You may apply the user function I supplied in this post to a different forum: [Calc BASIC]Function to convert attributes and links to html (View topic) • Apache OpenOffice Community 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.
ask283622extractURLdemo_1.ods
Comments welcome.
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.
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
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.)