Calc: Convert text to link

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?

Try to find the right settings to command Search & Replace

Update I am grateful to @GerritGriebel and @LonnieBest 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/t/calc-convert-text-to-link/1945 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

1 Like

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

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("&")

Thanks! That worked out nicely! :slight_smile:

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