After pasting a table from a web page into Calc that contains hyperlinks, how can I get the urls from the links?
I know I can ctrl-click the link to open the page, but what I want to do is to copy the url into another column using a formula. When I select the cell with the link, I see the text, but not the link. The text is the cell is clickable, so the link info must be present, I just can’t see it or access it via a formula.
I’m using Windows 7, and the latest version of LibreOffice Calc
Sorry! I just came about this old thread by accident, and as there was no answer to date, I want to point to this thread in another (and better structured) forum this archived copy of a thread in another (and better structured) forum. Please refer to my comment #8 there. The user function charged with the task in the demonstartion attached to that comment can as well be used to return one (the only?) URL as to return all the URL found in the one addressed cell as an array.
(Editing a year and a half later due to the comment below:)
I still don’t know a solution by standard functions. Considering a solution by user code I want to stress that the annoying aspect of the usage (not so much of the code) is that the code needs acces to the .Text property of the respective cell and therefore to the cell itself as an object, not just to the content that is passed by the calling formula (it’s evaluator to mbe precise).
See my insertion above to get access to the old thread.
The question itself was asked more than once in different forums. Since one of these forums has a structure better allowing for long-term access I once posted there an extended solution usable to get information about links and many kinds of text-attributes assigned to parts of cell contents. You find it here. The user function given there returns URL and anchor as html-code. You can easily change (reduce) that.
As you may know VBA functions have access to CellRange objects passed as simple references in a formula. If you want to “simplify” the task extremely, and don’t shy back from relying on the experimental VBA support, you can create a BASIC module containing the following code:
REM ***** BASIC *****
Option VBAsupport 1
Function firstURL(ByVal pCell As Object)
firstURL="fail"
On Error Goto errorExit
If pCell.Count>1 Then Exit Function
theCell = pCell.CellRange.GetCellByPosition(0,0)
theText = theCell.Text
theTextFields = theText.TextFields
For k = 0 To theTextFields.Count-1
theTF = theTextFields(k)
If theTF.URL<>"" Then
firstURL = theTF.URL
Exit For
End If
Next k
errorExit:
End Function
If no range object is passed, the error #VALUE! should be returned.
(I am not definitely sure if there can be non-URL TextFields in Calc cells. Therefore the Loop.)
The linked site is dead.
The very good forum I mentioned died from lack of official support. Thank “charlie.it” (Profile - charlie.it - Ask LibreOffice) there exists an archive (unfortunately not searchable). Here you find the thread I tried to link to. You may have a glance on the last post there (by myself).