Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 5th 07, 07:56 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 5
Default Can't make hyperlink function work for hyperlink to website

This has me baffled and I would greatly appreciate some help.

Column E contains URLs, and I'm trying to use the hyperlink function to create
hyperlinks in column F.

For example:

Cell E2 contains the url "http://www.somedomain.com". (My newsreader is
automatically making this a hotlink, but in Excel it is plain text.)

Cell F2 contains the formula "=hyperlink(E2)" and the result looks just like a
good hyperlink. It's blue and underlined. But nothing at all happens when I
click it. Neither a jump nor an error message. Same result if the formula is
"=hyperlink(E2,E2)".

Am I overlooking something really simple?

TIA

Frank

Frank Denman
Denman Systems

[Please delete the "x" from my email address]

  #2   Report Post  
Old February 5th 07, 08:07 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 5
Default Can't make hyperlink function work for hyperlink to website

Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems

[Please delete the "x" from my email address]
  #3   Report Post  
Old February 5th 07, 08:58 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 605
Default Can't make hyperlink function work for hyperlink to website

I used hyperlink function before and I just tried it again. It works fine for me. I use Excel 2003.

See if the following link gives you some inspiration.

http://www.mrexcel.com/archive/Formulas/19517.html

A line from the above link:

"The value in column 2 is exactly a link reference (not a true hyperlink -- rather a spec for a hyperlink)."

Epinn

"Frank B Denman" wrote in message news Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems

[Please delete the "x" from my email address]

  #4   Report Post  
Old February 5th 07, 09:26 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 605
Default Can't make hyperlink function work for hyperlink to website

Forgot to mention that there is a limit to the number of characters for the link - about 250. If it is more than 250, it may still show as a link (blue, underline etc.) but nothing happens when you click. However, the example you gave was not above the limit.

It seems to work for me both ways - a true hyperlink or a spec for a hyperlink.

Epinn

"Epinn" wrote in message ...
I used hyperlink function before and I just tried it again. It works fine for me. I use Excel 2003.

See if the following link gives you some inspiration.

http://www.mrexcel.com/archive/Formulas/19517.html

A line from the above link:

"The value in column 2 is exactly a link reference (not a true hyperlink -- rather a spec for a hyperlink)."

Epinn

"Frank B Denman" wrote in message news Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems

[Please delete the "x" from my email address]


  #5   Report Post  
Old February 5th 07, 09:51 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 5
Default Can't make hyperlink function work for hyperlink to website

Alas, that quote is going over my head.

I don't have any lookup tables. My worksheet is simply a database with columns
for Company, Address, Phone, Company_Web, etc. I imported the data from a text
file and the website addresses appear as plain text rather than as hyperlinks.

So I'm not sure in this context what "a spec for a hyperlink" means.

BTW, when the mouse pointer hovers over my non-functional hyperlinks, the
pointer changes to the expected hand with index finger extended, and the balloon
message "http://somedomain.com - Click once to follow. Click and hold to select
this cell" appears as expected.

Frank



On Mon, 5 Feb 2007 03:26:52 -0500, "Epinn" wrote:

Forgot to mention that there is a limit to the number of characters for the link - about 250. If it is more than 250, it may still show as a link (blue, underline etc.) but nothing happens when you click. However, the example you gave was not above the limit.

It seems to work for me both ways - a true hyperlink or a spec for a hyperlink.

Epinn

"Epinn" wrote in message ...
I used hyperlink function before and I just tried it again. It works fine for me. I use Excel 2003.

See if the following link gives you some inspiration.

http://www.mrexcel.com/archive/Formulas/19517.html

A line from the above link:

"The value in column 2 is exactly a link reference (not a true hyperlink -- rather a spec for a hyperlink)."

Epinn

"Frank B Denman" wrote in message news Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems

[Please delete the "x" from my email address]

Frank Denman
Denman Systems

[Please delete the "x" from my email address]


  #6   Report Post  
Old February 5th 07, 10:51 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 605
Default Can't make hyperlink function work for hyperlink to website

So I'm not sure in this context what "a spec for a hyperlink" means.


If I interpret it correctly, it is exactly what you have - plain text i.e. URL in plain text format and not blue and underline.

One word from your message stands out - imported. I typed in the URL.

I am curious as to what happens when you actually type in the URL manually into a blank cell on the same worksheet and hit enter. Does it turn into a hyperlink immediately? If the hyperlink function works well with the typed in URL, then the problem may lie with the imported data?? Some hidden control/print characters? But then you have the balloon etc.

Just a thought. Good luck!

Appreciate feedback when you have solved your problem so that I can congratulate you.

Epinn

"Frank B Denman" wrote in message ...
Alas, that quote is going over my head.

I don't have any lookup tables. My worksheet is simply a database with columns
for Company, Address, Phone, Company_Web, etc. I imported the data from a text
file and the website addresses appear as plain text rather than as hyperlinks.

So I'm not sure in this context what "a spec for a hyperlink" means.

BTW, when the mouse pointer hovers over my non-functional hyperlinks, the
pointer changes to the expected hand with index finger extended, and the balloon
message "http://somedomain.com - Click once to follow. Click and hold to select
this cell" appears as expected.

Frank



On Mon, 5 Feb 2007 03:26:52 -0500, "Epinn" wrote:

Forgot to mention that there is a limit to the number of characters for the link - about 250. If it is more than 250, it may still show as a link (blue, underline etc.) but nothing happens when you click. However, the example you gave was not above the limit.

It seems to work for me both ways - a true hyperlink or a spec for a hyperlink.

Epinn

"Epinn" wrote in message ...
I used hyperlink function before and I just tried it again. It works fine for me. I use Excel 2003.

See if the following link gives you some inspiration.

http://www.mrexcel.com/archive/Formulas/19517.html

A line from the above link:

"The value in column 2 is exactly a link reference (not a true hyperlink -- rather a spec for a hyperlink)."

Epinn

"Frank B Denman" wrote in message news Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems

[Please delete the "x" from my email address]

Frank Denman
Denman Systems

[Please delete the "x" from my email address]

  #7   Report Post  
Old February 5th 07, 11:19 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 605
Default Can't make hyperlink function work for hyperlink to website

Sorry, I meant to say nonprinting characters.

I added CHAR(7) to my URL and it still worked without me including CLEAN in the HYPERLINK function.

The only time it didn't work was when I padded 256 blanks to the end of the URL. But then I got the #VALUE error, not the balloon like you had. As soon as I included TRIM in the HYPERLINK function, it was fine.

I could only think of TRIM and CLEAN.

I am signing off and will leave you alone as I have run out of ideas.

Epinn

"Epinn" wrote in message ...
So I'm not sure in this context what "a spec for a hyperlink" means.



If I interpret it correctly, it is exactly what you have - plain text i.e. URL in plain text format and not blue and underline.

One word from your message stands out - imported. I typed in the URL.

I am curious as to what happens when you actually type in the URL manually into a blank cell on the same worksheet and hit enter. Does it turn into a hyperlink immediately? If the hyperlink function works well with the typed in URL, then the problem may lie with the imported data?? Some hidden control/print characters? But then you have the balloon etc.

Just a thought. Good luck!

Appreciate feedback when you have solved your problem so that I can congratulate you.

Epinn

"Frank B Denman" wrote in message ...
Alas, that quote is going over my head.

I don't have any lookup tables. My worksheet is simply a database with columns
for Company, Address, Phone, Company_Web, etc. I imported the data from a text
file and the website addresses appear as plain text rather than as hyperlinks.

So I'm not sure in this context what "a spec for a hyperlink" means.

BTW, when the mouse pointer hovers over my non-functional hyperlinks, the
pointer changes to the expected hand with index finger extended, and the balloon
message "http://somedomain.com - Click once to follow. Click and hold to select
this cell" appears as expected.

Frank



On Mon, 5 Feb 2007 03:26:52 -0500, "Epinn" wrote:

Forgot to mention that there is a limit to the number of characters for the link - about 250. If it is more than 250, it may still show as a link (blue, underline etc.) but nothing happens when you click. However, the example you gave was not above the limit.

It seems to work for me both ways - a true hyperlink or a spec for a hyperlink.

Epinn

"Epinn" wrote in message ...
I used hyperlink function before and I just tried it again. It works fine for me. I use Excel 2003.

See if the following link gives you some inspiration.

http://www.mrexcel.com/archive/Formulas/19517.html

A line from the above link:

"The value in column 2 is exactly a link reference (not a true hyperlink -- rather a spec for a hyperlink)."

Epinn

"Frank B Denman" wrote in message news Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems

[Please delete the "x" from my email address]

Frank Denman
Denman Systems

[Please delete the "x" from my email address]


  #8   Report Post  
Old February 5th 07, 03:22 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 11,058
Default Can't make hyperlink function work for hyperlink to website

try:

=HYPERLINK(TRIM(SUBSTITUTE(SUBSTITUTE(E2,CHAR(34), ""),CHAR(160),"")))

--
Gary's Student
gsnu200703


"Frank B Denman" wrote:

This has me baffled and I would greatly appreciate some help.

Column E contains URLs, and I'm trying to use the hyperlink function to create
hyperlinks in column F.

For example:

Cell E2 contains the url "http://www.somedomain.com". (My newsreader is
automatically making this a hotlink, but in Excel it is plain text.)

Cell F2 contains the formula "=hyperlink(E2)" and the result looks just like a
good hyperlink. It's blue and underlined. But nothing at all happens when I
click it. Neither a jump nor an error message. Same result if the formula is
"=hyperlink(E2,E2)".

Am I overlooking something really simple?

TIA

Frank

Frank Denman
Denman Systems

[Please delete the "x" from my email address]

  #9   Report Post  
Old February 5th 07, 06:09 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 5
Default Can't make hyperlink function work for hyperlink to website

If I type the URL into a cell, a functional hyperlink is automatically created.

Frank


On Mon, 5 Feb 2007 04:51:44 -0500, "Epinn" wrote:

So I'm not sure in this context what "a spec for a hyperlink" means.



If I interpret it correctly, it is exactly what you have - plain text i.e. URL in plain text format and not blue and underline.

One word from your message stands out - imported. I typed in the URL.

I am curious as to what happens when you actually type in the URL manually into a blank cell on the same worksheet and hit enter. Does it turn into a hyperlink immediately? If the hyperlink function works well with the typed in URL, then the problem may lie with the imported data?? Some hidden control/print characters? But then you have the balloon etc.

Just a thought. Good luck!

Appreciate feedback when you have solved your problem so that I can congratulate you.

Epinn

"Frank B Denman" wrote in message ...
Alas, that quote is going over my head.

I don't have any lookup tables. My worksheet is simply a database with columns
for Company, Address, Phone, Company_Web, etc. I imported the data from a text
file and the website addresses appear as plain text rather than as hyperlinks.

So I'm not sure in this context what "a spec for a hyperlink" means.

BTW, when the mouse pointer hovers over my non-functional hyperlinks, the
pointer changes to the expected hand with index finger extended, and the balloon
message "http://somedomain.com - Click once to follow. Click and hold to select
this cell" appears as expected.

Frank



On Mon, 5 Feb 2007 03:26:52 -0500, "Epinn" wrote:

Forgot to mention that there is a limit to the number of characters for the link - about 250. If it is more than 250, it may still show as a link (blue, underline etc.) but nothing happens when you click. However, the example you gave was not above the limit.

It seems to work for me both ways - a true hyperlink or a spec for a hyperlink.

Epinn

"Epinn" wrote in message ...
I used hyperlink function before and I just tried it again. It works fine for me. I use Excel 2003.

See if the following link gives you some inspiration.

http://www.mrexcel.com/archive/Formulas/19517.html

A line from the above link:

"The value in column 2 is exactly a link reference (not a true hyperlink -- rather a spec for a hyperlink)."

Epinn

"Frank B Denman" wrote in message news Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems

[Please delete the "x" from my email address]

Frank Denman
Denman Systems

[Please delete the "x" from my email address]

Frank Denman
Denman Systems

[Please delete the "x" from my email address]
  #10   Report Post  
Old February 5th 07, 06:15 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 5
Default Can't make hyperlink function work for hyperlink to website

Wow! That works! Now all I need to do is figure out why.

Thanks so much for this gift.

Cheers,

frank


On Mon, 5 Feb 2007 06:22:01 -0800, Gary''s Student
wrote:

=HYPERLINK(TRIM(SUBSTITUTE(SUBSTITUTE(E2,CHAR(34) ,""),CHAR(160),"")))

Frank Denman
Denman Systems

[Please delete the "x" from my email address]


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hyperlink function not working properly NB Excel Worksheet Functions 0 January 12th 07 07:18 PM
How to make userform work publically with hidden sheets? Zigball Excel Worksheet Functions 7 October 25th 06 03:30 PM
Improve Excel Help Text - Make easier to Find Function Refs RichardAllen Excel Worksheet Functions 0 April 10th 06 05:52 AM
Hyperlink function fails when using variable row number Balex Excel Worksheet Functions 5 April 8th 06 06:35 AM
hyperlink function Mary Excel Worksheet Functions 7 November 24th 04 02:58 AM


All times are GMT +1. The time now is 01:51 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017