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]

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]

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 ...
Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems

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

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 ...
Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems

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

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 ...
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]

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 ...
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]

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 ...
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]

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]

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 ...
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]

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]

Can't make hyperlink function work for hyperlink to website
 

Thank you for dropping by. It is a good suggestion.

I am as baffled as Frank mainly because he got the balloon and no error message.

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. <<

As I pointed out previously CHAR(7) had no negative impact and blanks gave #VALUE. Today I tried CHAR(34) and CHAR(160) and they gave the error "the address of this site is not valid ......" Wonder what other CHAR() we can try - something deceiving.

I am writing to share my experience. I recall something *similar* happened to me a few weeks ago. I clicked on a *true* hperlink (blue, underline) in a cell on an Excel worksheet and nothing popped up and no error message. So I thought it didn't work. However, when I clicked on Internet Explorer (already opened) on the task bar (at the very bottom of the screen), the web site was actually displayed. I clicked on the hyperlink four times and I had four sessions of the web site displayed. So, it actually worked despite no popping up. Last night, a pop up happened when I clicked on a hyperlink. I didn't change any Excel settings in between. I did do some cleanup e.g. cache.

Any insight to this?

Wonder if Frank had the browser opened before he clicked on the hyperlink.

Just a thought.

Epinn




"Gary''s Student" wrote in message ...
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]

Can't make hyperlink function work for hyperlink to website
 

Frank, now I can congratulate you! I am glad that you were detailed and mentioned "import" and it really caught my eyes. I had a feeling that it might have something to do with nonprinting characters but still surprised why the balloon showed. Anyway, I am happy for you.

I wonder if CLEAN works at all. Gary's student, would you please help me learn.

Thanks.

Epinn

"Frank B Denman" wrote in message ...
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]

Can't make hyperlink function work for hyperlink to website
 

Now all I need to do is figure out why. <<

CHAR(160) and CHAR(34) are nonprintable characters which I alerted you to.

http://www.htmlhelp.com/reference/charset/

CHAR(160) - non-breaking space
CHAR (34) - double quotes

Gary's student may be able to explain better. I thought of CHAR(160) but not CHAR(34). Good job.

Epinn

"Frank B Denman" wrote in message ...
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]

Can't make hyperlink function work for hyperlink to website
 

From the link I posted earlier:

"ISO-8859-1 explicitly does not define displayable characters for positions 0-31 and 127-159, and the HTML standard does not allow those to be used for displayable characters."

Epinn

"Epinn" wrote in message ...
Now all I need to do is figure out why. <<

CHAR(160) and CHAR(34) are nonprintable characters which I alerted you to.

http://www.htmlhelp.com/reference/charset/

CHAR(160) - non-breaking space
CHAR (34) - double quotes

Gary's student may be able to explain better. I thought of CHAR(160) but not CHAR(34). Good job.

Epinn

"Frank B Denman" wrote in message ...
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]

Can't make hyperlink function work for hyperlink to website
 

You are very welcome. I just followed Epinn's advise and made a formula out
of it.

Epinn deserves the credit for this one.
--
Gary's Student
gsnu200704


"Frank B Denman" wrote:

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]

Can't make hyperlink function work for hyperlink to website
 

Epinn deserves the credit for this one.

That is very kind of you. I was in the right direction but without your formula I might not be able to substantiate my suspicion. Collaboration works wonders. This is what the forum is about.

Previously I wrote: I wonder if CLEAN works at all.

I figure it out. CLEAN is tricky and does only half of a job.

The CLEAN function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional nonprinting characters. <<

I am glad I invested time in this thread as I have learned a few things.

Epinn

"Gary''s Student" wrote in message ...
You are very welcome. I just followed Epinn's advise and made a formula out
of it.

Epinn deserves the credit for this one.
--
Gary's Student
gsnu200704


"Frank B Denman" wrote:

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]