We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
1

how do I remove a hyperlink in calc?

asked 2016-03-06 19:38:50 +0200

Chuck Miller gravatar image

updated 2020-08-31 19:05:12 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
0

answered 2020-05-12 04:50:32 +0200

Chad Cloman gravatar image

According to another question, this inability to remove a hyperlink is a known bug.

Here is how I was able to remove the hyperlink:

  1. 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.)
  2. Select "Clear Direct Formatting". This will remove all formatting from the cell, including the hyperlinks.

An alternate method:

  1. Select the cell.
  2. 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.

edit flag offensive delete link more

Comments

"...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.

gtomorrow gravatar imagegtomorrow ( 2020-05-12 14:48:51 +0200 )edit
0

answered 2020-12-20 14:23:39 +0200

Lupp gravatar image

updated 2020-12-20 18:18:41 +0200

Quoting @gtomorrow : "'...this inability to remove a hyperlink is a known bug.' Link, please?"
Found: https://bugs.documentfoundation.org/s...
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 Sub
edit flag offensive delete link more
0

answered 2020-12-20 00:55:13 +0200

jbpark03 gravatar image
  1. Expand the cell in question (to make the button smaller than the cell frame)
  2. Right click the cell but not on the button.
  3. Choose "Backspace" among the listed items.

You are done.

image description

edit flag offensive delete link more
0

answered 2016-03-06 20:40:43 +0200

gtomorrow gravatar image

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.

edit flag offensive delete link more

Comments

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.

RonW gravatar imageRonW ( 2018-10-19 20:25:09 +0200 )edit

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.

Chad Cloman gravatar imageChad Cloman ( 2020-05-12 04:50:55 +0200 )edit

@gtomorrow, Edit your answer to tell which versions it works on.

LeroyG gravatar imageLeroyG ( 2020-12-20 21:01:04 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-03-06 19:38:50 +0200

Seen: 6,382 times

Last updated: Dec 20 '20