Ask Your Question

Function in calc to identify if a cell contains an hyperlink, also if it is behind the displayed text?

asked 2017-05-07 15:52:09 +0200

inJesus gravatar image

Would need this.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-05-07 19:55:38 +0200

Lupp gravatar image

updated 2017-05-07 22:44:17 +0200

If the supposed hyperlink in, say, A1 is ceated by the HYPERLINK() function you may roughly test for that using the FORMULA() function like in =ISNUMBER(FIND("HYPERLINK(";FORMULA(A1))). A cell containing a formula including "HYPERLINK(" as a piece of ordinary text will wrongly answer TRUE, however.

To get it with higher reliability, and also to get hyperlinks directly assigned to constant text content you need to rely on user code, I am afraid. Search the Cell.Tokens array for an Entry with Cell.Tokens(k).OpCode = 387 concerning the HYPERLINK() function, and Enumerate the contents down to TextPortions for (at least) one having the TextPortionType "TextField" and being associated with a TextField supporting the service "".

You may rework the user function cAttribs2Html contained in the attached demo to get a function with the logical result telling whether or not an URL was found.

Here is the mentioned demo.
See also this posting.

Editing with respect to the comments:
Check this demo for usability.

edit flag offensive delete link more


Hi Lupp

I looked into the demo. My entries look like in A4 but all the name/description is like "here", in gray.

I really have no idea about the solution you are talking about after the first paragraph, even some words.

Would be great if you or someone else make a solution for this for me (and for others)?

Or a workaround. Some Background: I export my bookmarks from Chrome in a html-file (no other option).

Best would be to have the name and the URL in two different cells beneath .

inJesus gravatar imageinJesus ( 2017-05-07 21:56:14 +0200 )edit

I found solutions for Excel here but Basic in LibreOffice does not like it.

inJesus gravatar imageinJesus ( 2017-05-07 22:16:02 +0200 )edit

"Best would be to have the name and the URL in two different cells beneath ."
This is quite different from what you originally wanted to achieve. I answered your original question and ...
Need some time now again.

Lupp gravatar imageLupp ( 2017-05-07 22:18:26 +0200 )edit

Dear Lupp, I have an alternative foundon This works even if have now idea how. It extracted the URLs to another sheet correctly. Now one problem remains: As also 500 folder names (without link) are in the colum of the 10000 bookmarks how to delete these non-bookmarks from the original column somehow automated? Then I can just copy the URLs besides in the next column. Done.

inJesus gravatar imageinJesus ( 2017-05-07 22:33:55 +0200 )edit

1) Sorry. I won't study that linked thread. I have to organise my time now.
2) "...beneath" is bad. Alongside is good.
3) Don't mix first! Use the 'Autofilter' now.

Lupp gravatar imageLupp ( 2017-05-07 22:50:01 +0200 )edit

I mark as answered now even if what works here is found in . Thank you anyway.

inJesus gravatar imageinJesus ( 2017-05-08 16:43:50 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-07 15:52:09 +0200

Seen: 1,592 times

Last updated: May 07 '17