Page 1 of 1

[Solved] How can turn URLs into hyperlinks in Calc?

PostPosted: Fri Feb 15, 2019 11:49 pm
by erichepperle
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.

Re: How can I automatically turn urls into hyperlinks in Cal

PostPosted: Sat Feb 16, 2019 2:21 am
by MrProgrammer
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

Re: How can I automatically turn urls into hyperlinks in Cal

PostPosted: Mon Feb 18, 2019 6:03 pm
by erichepperle
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:

Image

Image

Image

Image

Image

Image

Image

Re: How can I automatically turn urls into hyperlinks in Cal

PostPosted: Mon Feb 18, 2019 6:25 pm
by MrProgrammer
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.
Screen Shot 2019-02-18 at 10.58.52 .png
This dialog, which shows a single row, should show all the rows.
Screen Shot 2019-02-18 at 10.58.52 .png (8.6 KiB) Viewed 385 times

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.
Screen Shot 2019-02-18 at 10.11.42 .png
The correct Format Code

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.

Re: How can I automatically turn urls into hyperlinks in Cal

PostPosted: Thu May 16, 2019 5:55 pm
by THEBookMan
4 steps worked perfectly for me.
Thanks