Page 1 of 1
[Solved] How can turn URLs into hyperlinks in Calc?
Posted: 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
Posted: 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
Posted: 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:
Re: How can I automatically turn urls into hyperlinks in Cal
Posted: 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.
- 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 1343 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.
- 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
Posted: Thu May 16, 2019 5:55 pm
by THEBookMan
4 steps worked perfectly for me.
Thanks