Can't make hyperlink function work for hyperlink to website

[Frank B Denman]

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
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Frank B Denman]

Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Epinn]

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

Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Epinn]

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

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

Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Frank B Denman]

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

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

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

Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Epinn]

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

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

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

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

Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Epinn]

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



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

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

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

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

Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Gary''s Student]

try:

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

 

[Frank B Denman]

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

Frank

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

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

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

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

Forgot to mention this is Excel 2k3 SP2
Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Frank B Denman]

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

Thanks so much for this gift.

Cheers,

frank

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

Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Epinn]

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




try:

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

--
Gary's Student
gsnu200703

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
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Epinn]

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

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

Thanks so much for this gift.

Cheers,

frank

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

Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Epinn]

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

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

Thanks so much for this gift.

Cheers,

frank

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

Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Epinn]

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


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

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

Thanks so much for this gift.

Cheers,

frank

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

Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Gary''s Student]

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

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

Thanks so much for this gift.

Cheers,

frank

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

Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]

 

[Epinn]

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.

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

Epinn

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

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

Thanks so much for this gift.

Cheers,

frank

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

Frank Denman
Denman Systems
(e-mail address removed)
[Please delete the "x" from my email address]