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.
[Solved] How can turn URLs into hyperlinks in Calc?
-
- Posts: 5
- Joined: Fri Feb 15, 2019 10:37 pm
[Solved] How can turn URLs into hyperlinks in Calc?
Last edited by MrProgrammer on Wed Jul 31, 2019 11:38 pm, edited 1 time in total.
Reason: Tagged [Solved]
Reason: Tagged [Solved]
Eric L. Hepperle
OpenOffice version 4.1.5
Windows 10 64-Bit
OpenOffice version 4.1.5
Windows 10 64-Bit
- MrProgrammer
- Moderator
- Posts: 4906
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: How can I automatically turn urls into hyperlinks in Cal
Hi, and welcome to the forum.
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
[Tutorial] Text to Columns Q35/A35.erichepperle wrote:So, what I'm wondering is how can you turn every url in a Calc sheet into a hyperlink automatically?
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
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
-
- Posts: 5
- Joined: Fri Feb 15, 2019 10:37 pm
Re: How can I automatically turn urls into hyperlinks in Cal
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:
* 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:
- Attachments
-
- hyperlink-test.ods
- (12.9 KiB) Downloaded 113 times
Eric L. Hepperle
OpenOffice version 4.1.5
Windows 10 64-Bit
OpenOffice version 4.1.5
Windows 10 64-Bit
- MrProgrammer
- Moderator
- Posts: 4906
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: How can I automatically turn urls into hyperlinks in Cal
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: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.
→ → → 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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
-
- Posts: 107
- Joined: Wed Sep 30, 2015 10:03 pm
- Location: Houston, TX area
Re: How can I automatically turn urls into hyperlinks in Cal
4 steps worked perfectly for me.
Thanks
Thanks
Open Office 4.1.3 Win 10