Ask Your Question

How to extract the URLs of text with hypertext into the next column? [closed]

asked 2017-05-12 00:34:02 +0200

inJesus gravatar image

updated 2017-05-12 00:35:12 +0200

See my example. In reality I have 10000 bookmarks to extract. C:\fakepath\Example for question about extracting URLs.ods My goal is to leave column A as it is and have the URLs in column B.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-03 22:58:46.016677


Not so imported anymore as I found steps to get the desired result. But still interesting. Maybe others need it or I need it later and it is more easily applied than the solution I found with a lot of help from others. I will test your example file if it does what I wished when you will upload it for these reasons.

inJesus gravatar imageinJesus ( 2017-05-12 14:53:26 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-05-12 14:50:06 +0200

Lupp gravatar image

To achieve goals like the one under discussion here based on a Sub comes with some disadvantages:
1 - The Sub will need to know the cell range (including the sheet) on which to apply the search.
Suggested "macros" tend to simply not deal with this problem and to apply whatever they do to a constant range.
2 - The Sub will need to know an output range.
Programmed observing well proven standards it should send an alert if a non-blank cell gets concerned.
3 - The code is highly specialised -and will probably only run once.
4 - Doing it this way is against the "spreadsheet paradigm" which is to calculate results from input to formulae.

In this special case I suppose to know that the Original Questioner is also interested in closely related tasks. It should be desirable to know all these related tasks to be able to suggest a solution achieving them in one run, or to provide means capable of helping with more than one single aspect only.

In this case the OQ posted (at least) two questions (#94793, # 94882) related in this sense, but not explicitly telling it. He also stepped in to a very old thread (#50141) seeing it related to his problem, which it not is.

I made a new demo, not only to suggest a possible solution, but also to show in what way the knowledge of the relation between question can help to find adequate solutions - based on formulae (which is the Spredsheet-Do) and on built in tools of LibO.

As opposed to a Sub a Function can get the needed info via parameters in a perfect way when called from a spreadsheet cell.

The mentioned demo is attached here.

BTW: In the code line If Not isNull( oTextFields ) And oTextFields.getCount() > 0 Then a pair of parentehses is missing:
aIf Not isNull( oTextFields ) And (oTextFields.getCount() > 0) Then

edit flag offensive delete link more


This example works here. Good to know. Thank you Lupp.

BTW, my whole project is succesfully finished now. I have split up topics as the answers to a multistep solution in one thread would have overwhelmed me (even more). And it get beome confusing. But a workaround would be to receive suggestions in the complex question and then have several tasks needed explained in other threads. What do you think?

inJesus gravatar imageinJesus ( 2017-05-12 15:10:54 +0200 )edit

Very interesting stuff with the Links pulldown menu, showing a Listbox and Buttons etc. I didn't know that was possible in Calc.. However in this way the user has to perform a lot of actions just to fill 1 column with URLs. As mentioned above, this task is highly specialised for the user's case and will probably only run once. This would be an argument for using an ad-hoc macro to get the job done, instead of altering the sheet's infrastructure. BTW @Lupp, why are those parentheses necessary?

librebel gravatar imagelibrebel ( 2017-05-12 20:54:57 +0200 )edit

It was easy to do for me who is quite a beginner what I asked for in this question. I did not understand the answer or at least not all parts and also I am not interested in theory so much. But I tested the demo file, at least the part of it I needed, and saw it works. I copied the macro in my real project file. And copied the formula, referenced it to the right cells. Then copied it down with copy and paste. So I have got the URLs. Pasting with Ctrl Shift V into the next column.

inJesus gravatar imageinJesus ( 2017-05-12 21:31:28 +0200 )edit

Quoting@librebel: "...why are those parentheses necessary?"
They aren't. Sorry! Due to my obsession with being explicit i got into the habit of using parentheses around comparisons - and finally I forgot about how the precedence of operators is actually specified. (The default may not be reliably universal, however.)

Lupp gravatar imageLupp ( 2017-05-13 16:40:40 +0200 )edit

no prob @Lupp ... Happy coding :)

librebel gravatar imagelibrebel ( 2017-05-14 07:28:58 +0200 )edit

@Lupp, I too developed this obsession somewhere along the line. lol. :-)

EasyTrieve gravatar imageEasyTrieve ( 2017-05-14 17:42:51 +0200 )edit

A funny addendum: For a short time I used the undocumented alternative in LibO and its apply AND and/or OR in Calc as infix operators. This is not ruled by an order of precedence, however, It requires to put the operands in parentheses even if simple references or constants, not to talk of comparisons. Shall I talk of "infix functions"?

Lupp gravatar imageLupp ( 2017-05-14 21:03:30 +0200 )edit

talking about explicit .. i started omitting the "Option Explicit" statement from my BASIC code around the same time that i was diving into Python ... yup, learning Python causes a "loosening" of coding habits in other languages ..

librebel gravatar imagelibrebel ( 2017-05-14 23:33:42 +0200 )edit

So many languages I'm not just loose any more, I'm irregular.

EasyTrieve gravatar imageEasyTrieve ( 2017-05-15 03:09:38 +0200 )edit

answered 2017-05-12 07:19:24 +0200

librebel gravatar image

updated 2017-05-12 12:00:27 +0200

Hello @inJesus, to extract the URLs from the hyperlinks in your Column A to Column B, you could use a macro such as:

Sub Extract_URLs_From_Hyperlinks( iSheetIndex as Integer, iColumnHyperlinks As Integer, iColumnURLs As Integer )
REM <iSheetIndex>:          Sheet index ( 0 = Sheet1 ).
REM <iColumnHyperlinks>:    Column with the Hyperlinks ( 0 = A ).
REM <iColumnURLs>:          Column where the URLs from the Hyperlinks should be extracted to ( 1 = B ).
    On Local Error GoTo Exit_Sub
    Dim oSheet : oSheet = ThisComponent.Sheets.getByIndex( iSheetIndex )
    Dim oCell, oTextFields
    Dim strURL as String
    Dim i As Integer

    REM Traverse Rows:
    For i = 0 to 99999      REM 99999 = set here your maximum number of Rows to process...
        oCell = oSheet.getCellByPosition( iColumnHyperlinks, i )
        oTextFields = oCell.getTextFields()
        If Not isNull( oTextFields ) And oTextFields.getCount() > 0 Then
            strURL = oTextFields.getByIndex( 0 ).URL
            oSheet.getCellByPosition( iColumnURLs, i ).setString( strURL )
        End If
    Next i
End Sub

EDIT: working example attached ( This is your own uploaded file that i made working ) C:\fakepath\14945420052665621b.ods

edit flag offensive delete link more


Thank you.

Before using it; Did you try it yourself on the sample file or another example?

inJesus gravatar imageinJesus ( 2017-05-12 10:36:10 +0200 )edit

Yes, i tried this myself using your sample file .. so it's tested for 2 rows ...

librebel gravatar imagelibrebel ( 2017-05-12 10:40:56 +0200 )edit

Would you send me an example file where this is working?

inJesus gravatar imageinJesus ( 2017-05-12 11:43:35 +0200 )edit

Yes, i will upload a working example in my original answer.

librebel gravatar imagelibrebel ( 2017-05-12 11:55:25 +0200 )edit

Works great. Thank you.

inJesus gravatar imageinJesus ( 2017-05-14 18:37:50 +0200 )edit

Question Tools

1 follower


Asked: 2017-05-12 00:34:02 +0200

Seen: 2,924 times

Last updated: May 12 '17