[Solved] Change cells to hyperlinks

Discuss the spreadsheet application

[Solved] Change cells to hyperlinks

Postby trogne » Sat Feb 01, 2020 4:42 pm

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
trogne
 
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: change cells to hyperlinks

Postby MrProgrammer » Sat Feb 01, 2020 5:52 pm

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

Re: change cells to hyperlinks

Postby Zizi64 » Sat Feb 01, 2020 5:53 pm

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9543
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: change cells to hyperlinks

Postby trogne » Sat Feb 01, 2020 6:24 pm

@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
trogne
 
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: change cells to hyperlinks

Postby Zizi64 » Sat Feb 01, 2020 6:35 pm

Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9543
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Change cells to hyperlinks

Postby Villeroy » Sat Feb 01, 2020 6:41 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Change cells to hyperlinks

Postby trogne » Sat Feb 01, 2020 9:50 pm

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
trogne
 
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: Change cells to hyperlinks

Postby Zizi64 » Sat Feb 01, 2020 10:43 pm

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

It works for me in my LO 6.2.8 Writer/Calc
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9543
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Change cells to hyperlinks

Postby trogne » Sun Feb 02, 2020 5:07 pm

@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
trogne
 
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: Change cells to hyperlinks

Postby Zizi64 » Sun Feb 02, 2020 7:46 pm

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9543
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Change cells to hyperlinks

Postby trogne » Mon Feb 03, 2020 12:35 am

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
trogne
 
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: Change cells to hyperlinks

Postby Zizi64 » Mon Feb 03, 2020 7:27 am

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9543
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Change cells to hyperlinks

Postby trogne » Mon Feb 03, 2020 5:58 pm

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
trogne
 
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: Change cells to hyperlinks

Postby Lupp » Mon Feb 03, 2020 6:22 pm

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 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2913
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Change cells to hyperlinks

Postby trogne » Mon Feb 03, 2020 6:37 pm

See the file "links.ods".

Hope it's clear now.

Thanks
Attachments
links.ods
(16.75 KiB) Downloaded 20 times
OpenOffice 4 on Windows 10
trogne
 
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: Change cells to hyperlinks

Postby Lupp » Mon Feb 03, 2020 8:45 pm

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 25 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2913
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Change cells to hyperlinks

Postby trogne » Mon Feb 03, 2020 9:55 pm

@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
trogne
 
Posts: 16
Joined: Thu Nov 23, 2017 4:03 pm

Re: [Solved] Change cells to hyperlinks

Postby Zizi64 » Mon Feb 03, 2020 11:19 pm

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 19 times
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9543
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests