Ask Your Question
2

Calc: Convert text to link [closed]

asked 2012-10-08 11:25:30 +0200

EzMe gravatar image

updated 2013-06-15 00:41:59 +0200

manj_k gravatar image

I guess this is a simple question but I cannot seem to fix it. I've got a Calc sheet with about 5000 lines which have all lines like cell A1: W:\qt-reports\qt10-418.ods

I'd like to make these clickable links but I cannot seem to find how. I allready made collum which looks like: =HYPERLINK($A1)

but that sollution does not suite me very well sinds we got now two collums. I just like to make collum A1 clickable without manually change all the 5000 lines. Would that be possible?

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-18 15:39:47.203028

2 Answers

Sort by » oldest newest most voted
3

answered 2012-10-08 11:47:37 +0200

JohnSUN gravatar image

updated 2020-07-30 11:34:59 +0200

Try to find the right settings to command Search & Replace

Create HYPERLINK

Update I am grateful to @Gerrit Griebel and @Lonnie Best for pointing out the inaccuracy in this answer. Indeed, search and replace strings should be other.

If the cells contain only link text, then the Find string can be as specified (.*) or just .*, the Find string must contain a ampersand sign & instead of an "ampersand-one" &1

FR_1.png

If the cell contains link text and some kind of explanatory text, the Find&Replace parameters will be slightly different.

For example, if the contents of the cells looks like this

https://ask.libreoffice.org/en/question/6692/calc-convert-text-to-link/ Jump to link

then the Find string can be ([^ ]*) (.*$) and Replace string will be like as =HYPERLINK("$1";"$2") (in this case, instead of the ampersand, we need to use the dollar sign and the parameter number)

FR_2

You can read more about using regular expressions in the Help

edit flag offensive delete link more

Comments

In English =HYPERLINK("&1";"&1") In Portuguese =HIPERLINK("&1";"&1")

sanlemos gravatar imagesanlemos ( 2019-08-26 05:23:28 +0200 )edit

This answer is helpful, but slightly wrong, because it appends a "1" onto the end of each hyperlink causing ctrl+click to go to the wrong URL. Instead, the "Replace With" input needs to be: =HYPERLINK("&")

Lonnie Best gravatar imageLonnie Best ( 2020-07-21 18:22:45 +0200 )edit
0

answered 2012-10-08 13:18:20 +0200

EzMe gravatar image

Thanks! That worked out nicely! :)

edit flag offensive delete link more

Comments

For me it worked only after removing "1": =HYPERLINK("&")

Gerrit Griebel gravatar imageGerrit Griebel ( 2016-11-21 11:32:51 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2012-10-08 11:25:30 +0200

Seen: 14,777 times

Last updated: Jul 30