[Solved] Change cells to hyperlinks

Discuss the spreadsheet application
Post Reply
trogne
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

[Solved] Change cells to hyperlinks

Post by trogne »

I have a columns with many links, but they are just texts, not hyperlinks.

How can I change them to hyperlinks in bulk ?

Can I do this without the Hyperlink function ?

If I use the hyperlink function, how can I paste the resulting hyperlinks with only the values (the link itself, with the text) ?
Using paste special, I can only copy the link text.
Last edited by Hagar Delest on Mon Feb 03, 2020 10:34 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 4 on Windows 10
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: change cells to hyperlinks

Post by MrProgrammer »

trogne wrote:I have a columns with many links, but they are just texts, not hyperlinks. How can I change them to hyperlinks in bulk ?
For processing an entire column at once: [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, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: change cells to hyperlinks

Post by Zizi64 »

Edit the contents: hit an Enter at the end of the string.
If the strings are regular hyperlinks, then they will become to hyperlinks.


...or use the HYPERLINK function in an another column.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
trogne
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: change cells to hyperlinks

Post by trogne »

@Zizi64, this is for one cell at a time. I want to convert thousands of cells.

@MrProgrammer, the link describes the HYPERLINK function.
I know how to use it. I want to copy the results to another column, without the HYPERLINK function. The result must be the link, without the "=hyperlink" formula.
OpenOffice 4 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: change cells to hyperlinks

Post by Zizi64 »

Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Change cells to hyperlinks

Post by Villeroy »

trogne wrote:@MrProgrammer, the link describes the HYPERLINK function.
No, it doesn't.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
trogne
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: Change cells to hyperlinks

Post by trogne »

As I said, I know how to use the hyperlink function.

How can I copy the resulting links into a new column ? In that new column, I only want the values (link and link text), without the "=hyperlink" function.

How is that possible ? Paste special not working for me.
OpenOffice 4 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Change cells to hyperlinks

Post by Zizi64 »

Try it:
https://stackoverflow.com/questions/315 ... into-links

It works for me in my LO 6.2.8 Writer/Calc
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
trogne
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: Change cells to hyperlinks

Post by trogne »

@Zizi64
I know how to convert to a link, using "=hyperlink".
I want to copy the resulted links in other cells, WITHOUT the "=hyperlink" formula.
I just want the values (link), WITHOUT the formula.

How can I do that ? I cannot do this with paste special
OpenOffice 4 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Change cells to hyperlinks

Post by Zizi64 »

PLEASE read the linked article above. The described workaround method works without any formula!
(I have tried in my LibreOffice)
Here is some quote from the linked page.

Hopefully you found a solution in the 6+ years since your question, but it may help others searching for the same solution.

Converting them in openoffice-calc is a cumbersome process.

It's much easier to copy them to openoffice-writer and covert them there, then copy them back:

1. Copy and paste the list of urls into a openoffice-writer document.

2. From the Format menu, choose AutoCorrect, then AutoCorrect Options...

3. On the Options tab, click both the [M] and [T] boxes next to URL Recognition and click the OK button.

4. Select the list, go to Format > AutoCorrect > Apply.

5. Copy the url list back to openoffice-calc.

Hope this helps someone!
Last edited by Zizi64 on Mon Feb 03, 2020 8:19 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
trogne
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: Change cells to hyperlinks

Post by trogne »

This does not work well for me.
Even if it works, I don't want the links to show as links. I want to show text instead of "http" links.

I conclude there is absolutely no way of copy/paste "=hyperlink" links to new cells without the formula.
OpenOffice 4 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Change cells to hyperlinks

Post by Zizi64 »

This does not work well for me.
Even if it works, I don't want the links to show as links. I want to show text instead of "http" links.

I conclude there is absolutely no way of copy/paste "=hyperlink" links to new cells without the formula.
Please upload a small sample file with the "before" and the "after" state to demonstrate what you want really...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
trogne
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: Change cells to hyperlinks

Post by trogne »

Before to the right ("=hyperlink", from 2 other columns).
After to the left (full link, with the text, and no more "=hyperlink" and the 2 other oclumns, this was done manually).
bef.png

Here you see there's no "=hyperlink" involved :
aft.png
OpenOffice 4 on Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Change cells to hyperlinks

Post by Lupp »

1. Please attach (small) example.ods in such a case instead of these annoying images. We there see a call to HYPERLINK() referencing cells in the columns M and L. We don't see the content of these cells. This is simply wasting time.

2. What you show at the left is neither a link nor any linkable URL (URI) but the text to which once something may have been linked. In fact I would suppose it's simply the text from cell L5.

3. The subject you chose for this thread was "Change cells to hyperlinks". In fact you cannot change a cell to something else, but everybody should expect you wanted to make something a working link there. Now it seems you wanted to get the anchortext of a cell content previously being linked to something you still don't show.

Please make clear now what you actually want.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
trogne
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: Change cells to hyperlinks

Post by trogne »

See the file "links.ods".

Hope it's clear now.

Thanks
Attachments
links.ods
(16.75 KiB) Downloaded 140 times
OpenOffice 4 on Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Change cells to hyperlinks

Post by Lupp »

A serious question: WHY not use the HYPERLINK() function for the purpose?
If you intend to dispose the source columns after the creation of the linked texts you will one day need code again to get the URL_s back, won't you? If you keep the source data HYPERLINK() is the appropriate means.

There is no built-in tool for this. You need user code. See attachment.
Attachments
aoo100966createLinksByMacro_1.ods
(15.71 KiB) Downloaded 189 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
trogne
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: Change cells to hyperlinks

Post by trogne »

@Lupp Excellent, this is exactly what I was looking for.

I understand now it's not possible without a macro.

Your macro works perfectly.
OpenOffice 4 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Change cells to hyperlinks

Post by Zizi64 »

I understand now it's not possible without a macro.
There is no ways to realize all of users idea.

A single Calc cell can store more than one hyperlinks in a Textfield object.

Here is an another approach by a macro: The URL and the Representation properties will be modified in some predefined hyperlinks of the cell range - You can get the Textfields by index: Textfields(0)
Convert to hyperlink.ods
(14.32 KiB) Downloaded 165 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Post Reply