Concatenate and Hyperlinks

[Zack Ferraro]

While creating the grandchild of the mother of all
functions (It's complicated, but not the most complicated)
I've had many road blocks, becuase I'm a general newbie
when it comes to Excel, but not when it comes to figuring
out computer stuff. Especially Microsoft. That said, my
problem is:

This function's job is to compile the information in the
cells into an email, which was an easy task, but one of
the cells it grabs from has a hyperlink. Concatenate
doesn't grab this hyperlink, so I only get the "Friendly
name". (Which, although useful, isn't what I want.) The
other problem is that CONCATENATE() seems to have a limit
of what it can, well, concatenate. I can survive with the
small text that it seems to allow, but is there a way that
this auto e-mail can be as wordy as it needs to be?

 

[David McRitchie]

Hi Zack,
I know you've got a question somewhere, but don't know what it
is. One stumbling block you appear to have encountered is
extracting the hyperlink email address or URL from a hyperlink.

You can use this function to obtain the hyperlink that you
get if you copy and paste from a web page for instance,
or enter one from right-click, edit hyperlink. More of
similar things on my page
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#url

Install the following code in a standard module
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

usage:
=HyperlinkAddress(B14)

Are you trying to write a whole email letter to someone in
a cell (oops sounds like a prisoner). Are you trying to
put a large amount of data into a cell -- I would use rows
and loop through them you can find an example on
John Walkenbach's site.
Sending Personalized Email from Excel, Tip 86, Using Outlook
http://www.j-walk.com/ss/excel/tips/tip86.htm
I used that with send out some email with some customizations
for Email address, name, that person's url that linked to
my site, and an optional comment about person's site
into various places along with the basic canned information about
my old links and my new links to be updated..

You can find additional links on my mailmerg.htm page including
to Ron de Bruin's site where he is rather focused on email
Some Coding examples for use with Sendmail.
http://www.rondebruin.nl/sendmail.htm

As far as the amount of actual data you can put into a cell
and this is from HELP look up Specification as in specification limits

Length of cell contents (text)32,767 characters.
Only 1,024 display in a cell; all 32,767 display in the formula bar.

You can increase the number of characters displayed by include
some line feeds Alt+Enter to generate som hex 0010 characters

If you are not familiar with the Google Groups (newsgroups) archives
you should be. Some hints for usage in
http://www.mvps.org/dmcritchie/excel/xlnews.htm

 

[Zack Ferraro]

Sorry about the lack of a question. I tend to obfusticate
things.

The HyperLinkAddress() worked wonderfuly. And was almost
exactly what I wanted. The only problem is now I can't
seem to get a "friendly name" for the hyperlink.
=HYPERLINK(HyperLinkAddress($A2),$A2) just gives me
"http://defunkurl.com" where I want it to read "De-Funk"

I'm sure I just have to add one line of code into the code
you gave me, and I'll probably figure it out rather
quickly, I just wanted to say thanks for the fxn.

I'm learning to live with the size limitation, because I
want to work with as little scripting as possible, and do
this all by Formula, which, by j-walk.com's testament,
only seems to like 255 characters.

-----Original Message-----
Hi Zack,
I know you've got a question somewhere, but don't know what it
is. One stumbling block you appear to have encountered is
extracting the hyperlink email address or URL from a hyperlink.

You can use this function to obtain the hyperlink that you
get if you copy and paste from a web page for instance,
or enter one from right-click, edit hyperlink. More of
similar things on my page
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#url

Install the following code in a standard module
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

usage:
=HyperlinkAddress(B14)

Are you trying to write a whole email letter to someone in
a cell (oops sounds like a prisoner). Are you trying to
put a large amount of data into a cell -- I would use rows
and loop through them you can find an example on
John Walkenbach's site.
Sending Personalized Email from Excel, Tip 86, Using Outlook
http://www.j-walk.com/ss/excel/tips/tip86.htm
I used that with send out some email with some customizations
for Email address, name, that person's url that linked to
my site, and an optional comment about person's site
into various places along with the basic canned information about
my old links and my new links to be updated..

You can find additional links on my mailmerg.htm page including
to Ron de Bruin's site where he is rather focused on email
Some Coding examples for use with Sendmail.
http://www.rondebruin.nl/sendmail.htm

As far as the amount of actual data you can put into a cell
and this is from HELP look up Specification as in specification limits

Length of cell contents (text)32,767 characters.
Only 1,024 display in a cell; all 32,767 display in the formula bar.

You can increase the number of characters displayed by include
some line feeds Alt+Enter to generate som hex 0010 characters

If you are not familiar with the Google Groups (newsgroups) archives
you should be. Some hints for usage in
http://www.mvps.org/dmcritchie/excel/xlnews.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

While creating the grandchild of the mother of all
functions (It's complicated, but not the most complicated)
I've had many road blocks, becuase I'm a general newbie
when it comes to Excel, but not when it comes to figuring
out computer stuff. Especially Microsoft. That said, my
problem is:

This function's job is to compile the information in the
cells into an email, which was an easy task, but one of
the cells it grabs from has a hyperlink. Concatenate
doesn't grab this hyperlink, so I only get the "Friendly
name". (Which, although useful, isn't what I want.) The
other problem is that CONCATENATE() seems to have a limit
of what it can, well, concatenate. I can survive with the
small text that it seems to allow, but is there a way that
this auto e-mail can be as wordy as it needs to be?

.

 

[Zack Ferraro]

OH HOhohoho
Upon further reflection... I don't know how to make an
outlook message with a "Friendly Name". It doesn't seem to
like hyperlinks with text other than the URL. Oh well.
It's okay the way it is. There isn't a problem with your
function. Thanks a bundle!

-----Original Message-----
Sorry about the lack of a question. I tend to obfusticate
things.

The HyperLinkAddress() worked wonderfuly. And was almost
exactly what I wanted. The only problem is now I can't
seem to get a "friendly name" for the hyperlink.
=HYPERLINK(HyperLinkAddress($A2),$A2) just gives me
"http://defunkurl.com" where I want it to read "De-Funk"

I'm sure I just have to add one line of code into the code
you gave me, and I'll probably figure it out rather
quickly, I just wanted to say thanks for the fxn.

I'm learning to live with the size limitation, because I
want to work with as little scripting as possible, and do
this all by Formula, which, by j-walk.com's testament,
only seems to like 255 characters.

-----Original Message-----
Hi Zack,
I know you've got a question somewhere, but don't know what it
is. One stumbling block you appear to have encountered is
extracting the hyperlink email address or URL from a hyperlink.

You can use this function to obtain the hyperlink that you
get if you copy and paste from a web page for instance,
or enter one from right-click, edit hyperlink. More of
similar things on my page
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#url

Install the following code in a standard module
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

usage:
=HyperlinkAddress(B14)

Are you trying to write a whole email letter to someone in
a cell (oops sounds like a prisoner). Are you trying to
put a large amount of data into a cell -- I would use rows
and loop through them you can find an example on
John Walkenbach's site.
Sending Personalized Email from Excel, Tip 86, Using Outlook
http://www.j-walk.com/ss/excel/tips/tip86.htm
I used that with send out some email with some customizations
for Email address, name, that person's url that linked to
my site, and an optional comment about person's site
into various places along with the basic canned information about
my old links and my new links to be updated..

You can find additional links on my mailmerg.htm page including
to Ron de Bruin's site where he is rather focused on email
Some Coding examples for use with Sendmail.
http://www.rondebruin.nl/sendmail.htm

As far as the amount of actual data you can put into a cell
and this is from HELP look up Specification as in specification limits

Length of cell contents (text)32,767 characters.
Only 1,024 display in a cell; all 32,767 display in the formula bar.

You can increase the number of characters displayed by include
some line feeds Alt+Enter to generate som hex

0010

characters

If you are not familiar with the Google Groups (newsgroups) archives
you should be. Some hints for usage in
http://www.mvps.org/dmcritchie/excel/xlnews.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

.

.

 

[David McRitchie]

Hi Zack,
Thought that was a legitimate site, that I needed, and I actually
think that there is such a site. Too bad there isn't some
automatic registry based on META tags., but I guess it would
be too open to abuse.

A2: De-Funk [with hyperlink of http://defunkurl.com]

=HYPERLINK(HyperLinkAddress($A2),$A2)

worked for me comes up with message cannot locate server
Change to a legitimate site and if works fine has the correct
link and has the correct display.

BTW, did not find such a site but ran across
HTML Broken Link Finder/Fixer
http://mindprod.com/projhtmlbrokenlink.html
and practically everything it describes is in xenu mentioned at
the bottom of that article -- just installed xenu yesterday, and contrary
to the indication in the above article, it did identify bad links within a page.
I discovered though that you want to run Xenu 1 level deep and that
you want to look at the options so you report by (web) page instead of
by URL. Doesn't fix links but does recognize redirections, though
not mine -- probably because I have a 15 second delay instead of
immediate redirection. I have enough to work on just on the broken ones.
Find broken links on your site with Xenu's Link Sleuth (TM)
http://home.snafu.de/tilman/xenulink.html

 

[David McRitchie]

In that case you didn't even need the function.