Ask Your Question

How ro rename hyperlinks in spreadsheet smultanously [closed]

asked 2012-12-09 16:40:52 +0200

rrbd gravatar image

updated 2013-06-10 21:05:24 +0200

manj_k gravatar image

Hi, I have lots of hyperlinks in a spreadsheet what have to be renamed in the same way ("file:///G:/" to "file:///U:/"). Most elegant way would be some 'Edit -> Find and Replace', but I think that is not possible. Is there any other way, at least to find hyperlinks with a particular string in it?

CU, Rainer

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-19 12:35:43.279658


When I solve a similar problem, I open a workbook with the archiver (7-Zip) and use the Find/Replace directly in file content.xml.

JohnSUN gravatar imageJohnSUN ( 2012-12-09 17:21:28 +0200 )edit

Hi John, I already tried similar tricks with unzipping, but I have had too much trouble iwth the rezipped documents.

rrbd gravatar imagerrbd ( 2012-12-09 17:59:30 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2012-12-09 23:54:19 +0200

m.a.riosv gravatar image

If hyperlinks was made with Menu/Edit/Hyperlink, I think the only option is editing the ods file.

But if the hyperlinks show the link not other text, then it is possible convert the hyperlinks to text and add the HYPERLINK() function:

  • select the cells with hyperlink
  • clear the format (Ctrl-M)
  • Find & Replace:
    Change the url
  • Find & Replace to add the HYPERLINK() function.
    Search: .*
    Replace: =HYPERLINK("&")
    In more options activate regular expressions.

This is more flexible, beside it is possible create the hyperlink and the text to show from other cells, you can change it with F & R.

Syntax HYPERLINK("URL") or HYPERLINK("URL"; "CellText")

edit flag offensive delete link more


Thx for Ideas. I know HYPERLINK(), but that's too sophisticated for a simple Link collection. I submitted "Bug 58096 - Allow to find and replace (hidden) properties like hyperlinks, picture names and descriptions, ..." <>

rrbd gravatar imagerrbd ( 2012-12-10 18:43:38 +0200 )edit

answered 2012-12-09 17:27:11 +0200

m.a.riosv gravatar image

Why do you think it is no possible with F & R? Have you tried?

Search: file:///G:/ Replace: file:///U:/

Disable entire cell. In more options disable regular expression an activate search formulas.

edit flag offensive delete link more


Hm, that really works for you? Not for me with parallel installation of "LOdev". May be I should have written explicitly "Not text looking like hyperlink, but real hyperlink in a word not looking like the link itself?

rrbd gravatar imagerrbd ( 2012-12-09 18:07:38 +0200 )edit

Question Tools


Asked: 2012-12-09 16:40:52 +0200

Seen: 8,015 times

Last updated: Dec 09 '12