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

Discuss the spreadsheet application

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

Postby erichepperle » Fri Feb 15, 2019 11:49 pm

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.
Last edited by MrProgrammer on Wed Jul 31, 2019 11:38 pm, edited 1 time in total.
Reason: Tagged [Solved]
Eric L. Hepperle
OpenOffice version 4.1.5
Windows 10 64-Bit
erichepperle
 
Posts: 3
Joined: Fri Feb 15, 2019 10:37 pm

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

Postby MrProgrammer » Sat Feb 16, 2019 2:21 am

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
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3903
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

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

Postby erichepperle » Mon Feb 18, 2019 6:03 pm

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
Attachments
hyperlink-test.ods
(12.9 KiB) Downloaded 22 times
Eric L. Hepperle
OpenOffice version 4.1.5
Windows 10 64-Bit
erichepperle
 
Posts: 3
Joined: Fri Feb 15, 2019 10:37 pm

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

Postby MrProgrammer » Mon Feb 18, 2019 6:25 pm

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 393 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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3903
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

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

Postby THEBookMan » Thu May 16, 2019 5:55 pm

4 steps worked perfectly for me.
Thanks
Open Office 4.1.3 Win 10
THEBookMan
 
Posts: 80
Joined: Wed Sep 30, 2015 10:03 pm
Location: GB, WI (some sort of sport team lives around here)


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 8 guests