Turn link text into hyperlink?

English
,
#1

I received a CSV file that contains over 50 URLs. Unfortunately, LibreCalc does not recognize them as hyperlinks.

I went through and was selecting each link one-by-one (left control click) so I could copy/paste them in my browser when suddenly one line’s text automatically turned into a hyperlink and opened in my browser.

No matter what I tried, I could not reproduce the action and found nothing in the menus to automatically turn URL text into a clickable link.

Once the text is a link, Left-Ctrl + Left-Click opens the link, but how do I turn it into a link in the first place? Is there a way to get LibreCalc to automatically recognize text starting with “http:”/“https:” and display them as hyperlinks? What key-combo did I accidentally use to convert the text into a link?

TIA

#2

May by so?

Or F2->Space->Backspace->Enter (verify that URL Recognition is ON)

1 Like
#4

Thx. A bit time consuming, but explains how I “accidentally” turned text into a link (by simply hitting Enter at the end and letting Libre detect it as a link.)

#16

the link “behind” so

How to convert links into hyperlinks in bulk in Calc?
#3

Try these macros.

'––––––––––––––––––––––––––––––––– Hyperlinks ––––––––––––––––––––––––––––––––––
Sub SelectionInsertHyperlink()
	Dim oSelection As Object: oSelection = ThisComponent.CurrentSelection

	If Not (oSelection.supportsService("com.sun.star.sheet.SheetCellRange") _
	 Or oSelection.supportsService("com.sun.star.sheet.SheetCellRanges")) Then
		MsgBox "The range is not selected. Multiple selection is allowed." _
		 , MB_ICONEXCLAMATION, "Selection Error"
		Exit Sub
	End If

	Call ActiveSheetInsertHyperlink(oSelection)
End Sub

Sub ActiveSheetInsertHyperlink(Optional oSelection)
	Dim oRanges As Object, oCell As Object

	If IsMissing(oSelection) Then
		oRanges = ThisComponent.CurrentController.ActiveSheet _
		 .queryContentCells(com.sun.star.sheet.CellFlags.STRING)
	Else
		oRanges = oSelection.queryVisibleCells()  'visible only
	End If

	For Each oCell In oRanges.Cells
		Call CellInsertHyperLink(oCell)
	Next  
End Sub

Sub CellInsertHyperLink(oCell As Object)
'''	Remarks:
'''		• oCell.Text.insertTextContent(xRange, xContent, bAbsorb)
'''		<xRange> specifies the position of insertion.
'''		<xContent> is the text content to be inserted.
'''		<bAbsorb> specifies whether the text spanned by xRange will be replaced.
'''		• If True then the content of xRange will be replaced by xContent,
'''		otherwise xContent will be inserted at the end of xRange.

	Dim oField As Object
	Dim s As String, i As Integer

	oField = ThisComponent.createInstance("com.sun.star.text.TextField.URL")
Rem	Xray oField  'ScEditFieldObj
	s = oCell.String
	i = InStr(1, s, "http")
	If i > 0 Then
		oCell.String = Left(s, i - 1)
		oField.URL = Mid(s, i)
		oField.Representation = oField.URL
		oCell.Text.insertTextContent(oCell.Text.createTextCursor, oField, False)
	End If  
End Sub
1 Like
#5

Thx. How do I name/save this Macro so it is reusable in other documents? I copy/pasted it to the Macro Editor, but AFAICT, it’s saved to this one document, was saved with no name, and I was unable to get it to run. :frowning:

#6

Save macros to module:
Library.Module: ‘My Macros & Dialogs’.Standard

All macro names have already been assigned. The default name of the module is Module1. Name it Utils at your discretion.
Activate your sheet.
Alt+F11: Select ‘My Macros & Dialogs’.Standard, select ActiveSheetInsertHyperlink and run it.
Or select the range on the sheet and run the SelectionInsertHyperlink macro in the same way.

Macro core author is @sokol92. My edit.

#7

Help. Alt-F11 seems to have been remapped on my computer (by the graphics driver?) so pressing it does not appear to get me where I need to go. What menu option is it a shortcut for?

#8

Menu: Tools - Macros - Orgaize Macros - Basic…
Make sure it at least works in your document, and then move the macros to the specified library.

#9

Yes, the macro works, though I must hand select every cell one at a time that I want the macro to convert. Is there a way to have the macro scan the entire document and convert every link if found? If I must click every line one at a time, I might as well just use the edit/enter method first described. :frowning:
.
(It would be nice if this feature were built into the next update of Libre.)

#10

Yes, it is.

Either the selected range or the entire sheet is processed cell by cell. ActiveSheetInsertHyperlink is called by SelectionInsertHyperlink. Then ActiveSheetInsertHyperlink calls the CellInsertHyperLink procedure.

If it doesn’t work for you, post an example file. Perhaps others can help.

#11

Ah. I’m new to using Macros in Libre.
.
For any future readers, use “Menu: Tools - Macros - Organize Macros - Basic” as described previously, select the “ActiveSheetInsertHyperlink” routine and click “Run”.
.
Simply clicking “Run” on the entire macro is not enough. The macro was added to the system when first saved, so it will be available to other documents (no need to add script to every new file.)
.
Thx.

#12

why the hell dont you use the simple Search-replace-solution provided by @JohnSUN ??

1 Like
#13

I don’t think you tried his solution. It is not a “simple search & replace”. It’s not a “Search” at all.
.
It’s “click the line one at a time, edit the entry, and hit return” so LibreCalc automatically detects & converts the text into a link. No “Search & Replace” ever takes place.
.
Even if it could be done that way, what would you search for?

#14

I’m talk about the the provided link with simply::

→→Search and replace:
search for
http.*$
replace with:
=HYPERLINK("&")
with Options: 
regular Expression
1 Like
#15

@JohnSUN’s reply includes no such link. The only link in his post is regarding “URL Recognition” (the built-in feature in which a typed URL is turned into a link.)
.
I’m not sure where you are seeing a “provided link” referencing the use of the Search function.

#17

Of course it does:

the link “behind” so

#18

The macro will work even if the hyperlink is hidden in another text of the same cell, which means it is more flexible and you don’t have to remember anything. This is convenient.

#19

But the OP ask not for that.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.