I am new to LibreOffice and have opened a MS Excel spreadsheet that has a couple of hyperlinks in certain cells. How do I remove the hyperlink? In Excel it was simply a matter or right clicking on the cell and then selecting “remove hyperlink” – so simple.
According to another question, this inability to remove a hyperlink is a known bug.
Here is how I was able to remove the hyperlink:
- Right click on the cell, but ensure you’re -not- right-clicking the actual hyperlink. (If you right-click on the hyperlink, the “Clear Direct Formatting” doesn’t work for some reason.)
- Select “Clear Direct Formatting”. This will remove all formatting from the cell, including the hyperlinks.
An alternate method:
- Select the cell.
- Type Ctrl-M or select “Clear Direct Formatting” from the Format menu.
The problem with these methods is that you lose all of the other formatting, but you can use “Paste Special” to copy the formatting from another cell. This appears to be the only method that works at this time.
2 Likes
“…this inability to remove a hyperlink is a known bug.” Link, please?
I just checked this in LibreOffice 6.4.3.2 macOS and my original answer no longer works (it did when I originally answered this question). Your answer seems to be now the only way to clear a hyperlink. Just a tip for you, though: instead of messing with Paste Special you can 1) define a style, 2) modify Default Style or 3) use the Clone Formatting paintbrush icon in the toolbar.
“Clear Direct Formatting” remove hyperlink from a single cell, but does not work if you have selected several cells, then removes other formatting but not hyperlink.
Select the cell with the hyperlink and click the Link button in the toolbar (Ctrl-K/Cmd-K) and remove the link from the Target line there. Hope that helps.
Firstly being a newbie I don’t know what the Link button looks like. Tried to find it but no good. The Ctrl-K option brings up the Hyperlink options but nothing shows in the fields so consequently nothing is removed. The cell is still highlighted and suggests using Ctrl key to follow hyperlink when hovered over. I have been able to turn off the URL recognition so no more Hyperlinks are created but I would really like to tidy up my spreadsheet by removing those that are here already.
I agree. This answer simply doesn’t work. Even if you manage to get the Link dialog for the actual link, deleting the URL does not delete the hyperlink.
- Expand the cell in question (to make the button smaller than the cell frame)
- Right click the cell but not on the button.
- Choose “Backspace” among the listed items.
You are done.
Quoting @gtomorrow : “’…this inability to remove a hyperlink is a known bug.’ Link, please?”
Found: https://bugs.documentfoundation.org/show_bug.cgi?id=66694
There surely are additional threads in the bug tracker. tdf#66694 is more than 7 years old, and has a respectable ‘See also’.
I cannot see the reasons for the delay with this obviously relevant feature. One may be the fact that hyperlinks are very differently implemented in Calc and in Writer (e.g.)
However, I made a tour into the API, and have to admit that I didn’t find an easy way to do by user code for Calc what’s needed. It’s rather simple to “destroy” the links if partial formatting of cell texts is meaningless. To remove links (more than one per cell probably) from a cell preserving diffrent formatting of parts is rather complicated, and due to strange observations the code I ended up with may not be exactly reliable.
See yourself and post your better ideas:
Sub removeLinkageFromCellTextFieldURLContentIgnorigPartialFormatting(Optional pRgs)
REM Simple, but crude. Formatting of text portions not preserved.
If IsMissing(pRgs) Then pRgs = ThisComponent.CurrentSelection
rgs = pRgs.queryContentCells(4)
For Each rg In rgs
uR = rg.Rows.Count - 1
uC = rg.Columns.Count - 1
For c = 0 To uC
For r = 0 To uR
crCell = rg.getCellByPosition(c, r)
For Each l1 In crCell
For Each l2 In l1
If l2.TextPortionType="TextField" Then
If l2.TextField.supportsService("com.sun.star.text.textfield.URL") Then
s = crCell.String
crCell.Formula = ""
crCell.String = s
Goto nextr
End If
End If
Next l2
Next l1
nextr:
Next r
Next c
Next rg
End Sub
If the following macro is location=document:
Strangely the Sub works for me as expected also for cells containing more than one link and having differently attributed text portions if I start it from the IDE, but hangs if I call it via the UI.
Having location=application the issue vanishes. Tell me, please, if you find the reason.
Sub formatPreservingRemoveLinkageFromCellTextFieldURLContent(Optional pRgs)
If IsMissing(pRgs) Then pRgs = ThisComponent.CurrentSelection
rgs = pRgs.queryContentCells(4)
For Each rg In rgs
uR = rg.Rows.Count - 1
uC = rg.Columns.Count - 1
For c = 0 To uC
For r = 0 To uR
crCell = rg.getCellByPosition(c, r)
tfs = crCell.TextFields
uTF = tfs.Count - 1
URLcount = 0
For j = 0 To uTF
jTF = tfs(j)
REM See below!
If jTF.URL<>"" Then URLcount = URLcount + 1
Next j
While URLcount>0
For Each l1 In crCell
For Each l2 in l1
If l2.TextPortionType="TextField" Then
tf = l2.TextField
REM Strange: As long as I look at l2 I can see the service TextField.URL for the TextField.
REM The variable tf doesn't know that.
If tf.URL<>"" Then
tc = crCell.createTextCursorByRange(l2.Start)
l2.String = tf.Representation
crCell.removeTextContent(tf)
URLcount = URLcount - 1
Exit For
End If
End If
Next l2
If URLcount=0 Then Exit For
Next l1
Wend
nextr:
Next r
Next c
Next rg
End SubFWIW, I found in 7.6.4.1 that the Ctrl-M method works well, even with multiple cells selected.