I want to MURDER all the hyperlinks in a spreadsheet!

English
#1

I tried following Google AI “instructions” for removing all hyperlinks in a spreadsheet, but it didn’t. All it did was remove all formatting…and left the hyperlinks still very much alive.

I want my spreadsheet to ignore email addresses and anything that even remotely resembles a hyperlink. This spreadsheet is meant to store purely enriched text data; no calculations or navigations are to be performed. How do I get calc to “understand” that? All I want to enter, copy, and paste is TEXT data.

Using LibreOffice 25.2.2.2 & Windows 11 x64 Home 22H2 (10.0.26100.3915)

Wanderer led me to the solution in thje provided link: Tools> AutoCorrect Options> Turn off URL recognition

#2

Some things that will be helpful are your Operating System and LibreOffice Version all four digits. I.e. 25.2.2 for example.

1 Like
#3

Copy the cells having hyperlinks.
Paste-special values only.

#4

Here’s a great post by @Lupp on this topic.
Since the bug tdf#66694 is not fixed today, here’s a modified version of @Lupp 's macro that removes hyperlinks while preserving other formatting within the cells.

Sub RemoveHyperlinks(Optional oRanges)
  Dim oCell As Object, oPar As Object, oTextPortion As Object, bCont As Boolean
  If IsMissing(oRanges) Then  oRanges = ThisComponent.CurrentSelection
  oRanges = oRanges.queryContentCells(512)   ' it was 4+256. Thanks to @Lupp.
  For Each oCell In oRanges.Cells
    bCont = True
    Do While bCont 
      bCont = False
      For Each oPar In oCell
        For Each oTextPortion In oPar
          If oTextPortion.TextPortionType="TextField" Then
            If oTextPortion.TextField.supportsService("com.sun.star.text.textfield.URL") Then
              oTextPortion.setstring oTextPortion.TextField.Representation
              bCont=True
              Exit For
            End If
          End If
        Next oTextPortion
      Next oPar
    Loop  
  Next oCell
End Sub
1 Like
#5

Set your columns to be formatted as Text and switch off autocorrect entirely or only url-recognition.

https://help.libreoffice.org/latest/en-ZA/text/shared/01/06040100.html

#6

LibreOffice 25.2.2.2 & Windows 11 x64 Home 22H2 (10.0.26100.3915)

#7

You are a lifesaver. I feel so silly not having to think that I could turn off UR: recognition in Tools. Now, beside not opening a browser anymore, the text doesn’t change, stays the same. I started using spreadsheets before Excel was released, started on Lotus 1-2-3. Began using Excel in 1984, but I’ve been using open-source suites only for…I dunno, maybe fifteen years? But, I am totally new to LibreOffice, got fed up with the Apache garbage.

Thank you so much, Wanderer.

1 Like
#8

…but, how does unformatted text still not get recognized as a URL…unless recognition is turned off? Default font is same as Arial or whatever when calc sees an address.

#9

@sokol92: Did you test the working of cell falg 256 (=2^8)? oRanges.queryContentCells(4+256) finds every cell with ordinary content independent o partial formatting.
The addition of flag values should IMO work like an OR, and as far as I can see, it does.
But oRanges.queryContentCells(256) doesn’t find cells with hyperlinks or otherwise partially formatted content.
See tdf#137667.

#10

Hello, Wolfgang!
Thanks for the note and the link to the bug report (I “subscribed”).
It seems that hyperlinks are successfully removed with the queryContentCells(512) parameter. I made a change to the macro. If possible, please check it on your own examples.

#11

I did so under my “clean profile” with 25.2.2.2.
It worked, but there were strange observations seemingly unrelated to yor macro (with flags=512).
Mainly: Some hyperlinks (https:// or http://) I had created for parts of a cell content only worked under additional conditions, and that may not be reproducible.
Personally I use hyperlinks in Calc now very rarely, and if I do, it’s mostly vnd.sun.star.script//... and/or linkage by HYPERLINK(). I will therefore not go deeper into this matter again.
Regarding my observations and the open mentioned bug, I suspect the field “mined”, and would no longer recommend user code for the task.

#12

Thank you!
I tested the macro on files similar to the attached one (text copied in the browser and pasted via the clipboard).

Writer Features.ods (11.3 KB)

#13

If the killer instinct is wearing off, and a spreadsheet is needed to manage or edit hyperlinks, my suggestion as shown in the attached sheet might be of interest.
disask121728UnrecognizedAactivatableHyperlinks…ods (23.2 KB)

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