Apache OpenOffice Community Forum

There are a many tutorials for Excel and a few for Calc explaining how to create links with autoformat menu options. But, those instructions don't work for a document that already has a list of links.

So, what I'm wondering is how can you turn every url in a Calc sheet into a hyperlink automatically? Given a document with 200 urls all in the same column, for instance. I believe Google Sheets already has this functionality as an application of "Styles". But, I've not been able to find any guidance on doing this in Calc.

Thanks.

Hi, and welcome to the forum.

erichepperle wrote:So, what I'm wondering is how can you turn every url in a Calc sheet into a hyperlink automatically?

[Tutorial] Text to Columns Q35/A35.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

Thanks @MrProgrammer. I tried your suggestion:

* Select the column with the web addresses then: Format → Cells → Numbers → Format Code → =HYPERLINK("\"@\"")
* Select the column with the web addresses then: Data → Text to Columns → OK
* Select the column with the web addresses then: Format → Default Formatting
* Select the column with the web addresses then: Data → Text to Columns → OK

But, it didn't work. Or possibly I'm misunderstanding the instructions.

Following are some screenshots showing my progress. Notice I get "Err:508" on the last step. Not sure if that is important or not. I've seen that before when entering commas in formulas (Calc uses semicolons not commas), but I didn't enter any commas manually. Any idea what I might be doing wrong? I've uploaded my test document for your convenience as well.

Screenshots:

erichepperle wrote:I tried your suggestion:
* Select the column with the web addresses then: Format → Cells → Numbers → Format Code → =HYPERLINK("\"@\"")
…
But, it didn't work. Or possibly I'm misunderstanding the instructions.

Thank you for the detailed problem documentation. You have misunderstood the instructions. First, the dialog below which showed you a single row in the Text to Columns dialog means that you failed to follow this admonition:
→ → → Be sure to select the column, for example click the A above cell A1, in each of the four steps. Next, the format code is incorrect. Here is what is in the tutorial:
→ → → If you do have the http:// prefix in the cell, use format code "=HYPERLINK("\"@\"")" instead.

The format code is pink. Note that the outside quotes are pink. The color is to show that they are part of the format code. I am confident the instructions will make this conversion for you. I tested them when I created the tutorial and tested them again today. This is what you want. If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

4 steps worked perfectly for me.
Thanks