How to convert links into hyperlinks in bulk in Calc?

[michaldybczak]

All pasted links in a cell are text only. Creating hyperlinks is awfully tiresome and impossible task when one has to change hundreds of links to clickable hyperlinks.

In the past, I found some macro that did it. I basically marked the cells, run the macro and all links became clickable. Unfortunately, when moving to another laptop, I lost that macro, and now I can’t find it. Does anyone know such macro?

Also, I’m struggling with adding a new macro. I somehow managed it before, and I will figure it out now, but it’s not intuitive.

[JohnSUN]

Calc: Convert text to link

Try to find the right settings to command Search & Replace [Create HYPERLINK] Update I am grateful to @GerritGriebel and @LonnieBest for pointing out the inaccuracy in this answer. Indeed, search and replace strings should be other. If the cells contain only link text, then the Find string can be as specified (.*) or just .*, the Find string must contain a ampersand sign & instead of an “ampersand-one” &1 [FR_1.png] If the cell contains link text and some kind of explanatory text, the Fin…

[michaldybczak]

Thanks @JohnSUN , but that doesn’t work for me. I get only errors and this simply doesn’t do what it supposes to do. Maybe the formula is named differently in my language, but how could I find it?

Macro exists, and it was copy, paste, save and use. Simple and working. This has to be a few- click-no-brainer solution that works for many rows/columns at the same time. Quick and easy.

Or alternatively, when pasting any links, they should become clickable links automatically. This is how it worked in Excel.

I’m angry at myself that I didn’t save the macro in for future use.

It boggles my mind that it can’t be done easily in libreoffice. It should be even one click tool.

If you send a spreadsheet with a lot of links, people need to have them in clickable format, otherwise the document will be useless or very tiresome to use. Also for presentation, clickable links is the only thing that makes sense.

[JohnSUN]

@michaldybczak

Maybe the formula is named differently in my language, but how could I find it?

Go to Options, check this checkbox.

Follow the replacements recommended in the previous link. Get working formulas with the HYPERLINK() function. Go back to Options and uncheck the checkbox. Now the function is written in your language and still works.

[michaldybczak]

It works half-way.

For example, I have a link:

https://allegro.pl/oferta/lampa-liniowa-oprawa-cabail-40w-4000k-neutralny-1-2m-czarna-samsung-led-13623716743

Find: .*
Replace: =HYPERLINK("&")

The result is a cell with:

=HYPERLINK(“https://allegro.pl/oferta/lampa-liniowa-oprawa-cabail-40w-4000k-neutralny-1-2m-czarna-samsung-led-13623716743”)

The problem is, that it doesn’t display as a link (no underline and color). It works when clicked with ctrl, but it doesn’t follow the standard hyperlink formatting.

[michaldybczak]

Wow, I think I found solution in other topic:

Turn link text into hyperlink?

the link “behind” so

Here is the macro:

'––––––––––––––––––––––––––––––––– 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

When I pasted it into some blank macro, and run in the document, ALL text links changed into hyperlinks. Didn’t have to mark rows or columns, or text. Just one time run in a document and it’s done. It even works better the previous macro I used!

Still, libreoffice should make it a tool to mark and click, to turn text links into hyperlinks. It boggles my mind that it is not a standard, and we need to look for macros to do that simple and basic action.

[vib]

This can be done with a macro as well.
Calc Convert link text to Hyperlink is an example done in python using OOO Development Tools which is also available as an extension.

See Also: Garlic Secrets example. Cell Texts example. macro code example.

[michaldybczak]

I’m not a programmer, so I can’t make head or tail out of it. My skill is to record macro or create one with copy/paste. Which for the most part can get me where I want, but first I need to find a proper macro. I’m also more experienced with Excel macros, but I guess I have to figure it out on libre too.

I’ll try to follow the scrip as described. Thanks @vib .

Libreoffice should implement it as a tool which we can click. At the moment, the instruction is not clear enough for me, but I hope I will figure it out with some time and experiments…

[michaldybczak]

I have problem with this step:

To run the macro demo open this project in Codespace or Dev Container and run the following macro in Calc:

I’m not a programmer, so what I get is that I need some specialized apps to open this scrip. I also have no idea how running a script from some app will run it in Calc as well. I can assume that running this script will create the macro that I can use in Calc. Why not publish the macro itself? This feels as unnecessary convoluted and not suited for average user.
Basically, I have no idea what that means. I could try to execute it, but it would probably end badly, as I have never done anything like this.

[vib]

A Codespace is a Development environment that can be run in a web browser or other code editors.

The Live LibreOffice Python UNO Examples like Live LibreOffice Python can be run in a Codespace. This requires a Github account. If you have a Github account you can start the Codespace and run LibreOffice directly in your web browser with the examples and other code.

See the wiki for more information.