Copy hyperlink from text, from Sheet2 to Sheet1

I have created a formula that copies data from one sheet to another (=IF(NOT(ISBLANK($Sheet2.I12)),$Sheet2.I12,"")). All is well, it works great, except for the links. Hyperlinks are not formatted so they appear as simple text (this i solved with =IF(NOT(ISBLANK($Sheet2.B12)),HYPERLINK($Sheet2.B12),""). The solution is great for a single link in a cell, but i have a text that contains a link that is not formatted.
How can i make the partial text turn into a link? (Hi there, “click here!” <–hyperlink)

My vision is to modify what i need in Sheet2 and have it appear in Sheet1, links and all.

Hope someone can help me. :v:

[[This Q&A software has bad habits, and is by far not as clever as it seems to be supposed to be. Let me decide myself if I want to delete a post that was inadvertently published as an answer, and instead to post it as a comment.]]

I would need this explained to more detail - and best exemplified with an example .ods showing e.g. a “what I have” and a “what I want”.
“Links” are not quite sufficiently described by the word “link”. Every link has a .Representation and a .URL in Calc. Should this be pasted into a target cell always together? Or are there some steps of processing expected? How shall additional TextPortions contained in a cell (and not linked) be treated?
It’s obvious that a formula can’t return text containing linked portions and/or differently formatted portions of text to the cell it is contained in. In other words: You can’t use formulas the way it’s typical for spreadsheets, because the textual result of the used formula always has a unified format. And the HYPERLINK() function can only assign one URL to the displayed string.
Thus there will occur conflicts with basical properties and limitations of spreadsheets even if you take the trouble of writing UserCode.
You probably want to reconsider the design of your sheets, in specific the question if you actually need more than one linked URL in a single cell in your Sheet1. This is the crucial point concerning the need of rather sophisticated Code for the creation and insertion of TextField objects.
(Yes. I can do it. But these are your sheets, and you will be the one who has to maintain them and to adapt them to changing needs and situations.)

Hi Lupp,

I’ll do my best to explain without a file (this time).
In Sheet1 i have a direct link to cells in Sheet2.
Data that i add in Sheet2 will appear directly in Sheet1.
Sheet2 has one cell that explains some things and may contain at the end a “click here for more info.” (that text at the end is a link to an external site, like
I want to copy the whole text, link and all to Sheet1.

Till now, i’ve been copy-pasting that specific cell from Sheet2 to Sheet1. If i can do this automatically it would save a lot of precious time. It’s not amust, but i prefer to be efficient.

As for the macros, i’m not working on the file alone, so i’m not sure everyone would want to install a macro. That is why i prefer some kind of formula.

Are these “links” referencing the cells? (Not of much meaning in the context. Just about terminology.)

Reasonable, but not feasible. A formula returns a result to the cell it’s placed in, and that result, if text, always is … See above.
Even if resorting to UserDefinedFunction is not onlY problematic for the reason you gave yourself. Since the calling formula can’t reside in the result cell, the actually intended main effect would formally be a side-effect, and such side effects of functions are basically prohibited (if occurring in the same sheet at least). Calling the respective formula from a cell in the other sheet, however would be allowed, and this would abandon the need of installing macros locally if exection of document macros is permitted. (See explanations by @eeigor). The macro itself could be much simplified due to your recent explanation.
See attached example. To run it set MacroSecurity to ‘Medium’ in advance.
copyCellByFormulaExample.ods (17.6 KB)

Note: Any OO.o or AOO doesn’t sufficiently support VBA to interpret the parameters of the UDF correctly.


I want to thank you both, eeigor and Lupp, for your help and patience. If i could give you both the solution mark i would, so first come first serve.
The solutions you guys gave is good, but i doubt that will fly with my coworkers. That is a me/us problem.
Thank you again and i hope to get the same kind of help next time.

Take care! :v:

Select a range and call the macro SelectionInsertHyperlink. Or don’t select any, but activate the sheet and call the ActiveSheetInsertHyperlink. All nested links become hyperlinks.

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

	If Not (oSelection.supportsService("") _
	 Or oSelection.supportsService("")) Then
		MsgBox "The range not selected. Multiple selection 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 _
		oRanges = oSelection.queryVisibleCells()  'visible only
	End If

	For Each oCell In oRanges.Cells
		Call CellInsertHyperLink(oCell)
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("")
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

We do everything on the spot, you don’t need a second sheet. You can copy the sheet yourself at any time: before or after conversion.

UPD. Remarks
The cell text is on the left, the link is on the right as in your example. If not, the code needs to be changed.
The BASIC functions here are the same as the sheet functions of the same name, and you should be able to understand what they do.

oCell.String = Left(s, i - 1)  'text
oField.URL = Mid(s, i)  'link

You can traverse the link to the first space and assign the found text fragment to the URL. But then you have to move the cursor.
Either way, this is the way to solve your problem. :v:

Hi Eeigor,

Thanks for your answer.
Sadly it is very complicated for me. I tried my best to follow what you did, with a bit of “google-ing” i managed to install the macro. Sadly i can’t get it to work.
I was hoping for something more simplistic, like a function.
To be clear, i really appreciate the time and effort you put into your answer, but it’s very complicated for me. :v:

Copy and paste the macros into the My Macros & Dialogs library - Standard - e.g. Module1.
Снимок экрана от 2023-03-30 22-28-50

Alt+F11 - Select a macro - Run

This needs to be dealt with. It will open up a lot of new possibilities. You don’t have to write macros. You just have to learn how to call them.

UPD. Download the guide.
See: Chapter 13 Macros.

Thanks for the guide.
I’ve already installed the macro, the problem is i either can’t get it to work, or it doesn’t work.
I’ve made a new file with 2 sheets. Added a phrase with a link at the end, used the macro on the cell, then tried to paste it into the second sheet. It was a plane text. Tried running the macro on the new cell, but nothing happened.
I’m still new to Calc, so please be patient with me. The formula i gave above was done in 1 day of searching and trying to see what fits.

Perhaps, macros are disabled.