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

Would need this.

edit retag close merge delete

Sort by » oldest newest most voted

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 "com.sun.star.text.TextField.URL".

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.

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

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 .

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

I found solutions for Excel here https://duckduckgo.com/?q=extract+hyp... but Basic in LibreOffice does not like it.

( 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.

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

Dear Lupp, I have an alternative foundon https://www.experts-exchange.com/ques.... 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.

( 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.

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

I mark as answered now even if what works here is found in https://www.experts-exchange.com/ques... . Thank you anyway.

( 2017-05-08 16:43:50 +0200 )edit