Workaround for HYPERLINK argument length limit

[Dave Booker]

The arguments for a HYPERLINK() worksheet function seem to be limited to 256
characters. Is there a workaround for this?

I need to embed functions like this
=HYPERLINK("mailto:[email protected]?subject=My Subject&body="&A1)
in a worksheet, and I need it to accept very long body arguments. If the
argument in this case gets much longer than 200 characters, the function
becomes a #VALUE.

Also, I can't use macros because of all the security issues the worksheet
users will encounter.

 

[Epinn]

Yes, there is a limit to the number of characters (about 248?)

I put certain data/info in a cell. I can refer to the cell direct or use name definition.

Insert>Name>Define

e.g. In cell A1 I type http://www.yahoo.com and define A1 as YH.

=Hyperlink(YH) or =Hyperlink(Sheet1!A1)

This saves quite a few characters. You can use multiple cells and concatenate using &.

See if this link helps.

http://exceltips.vitalnews.com/Pages/T0463_Dynamic_Hyperlinks_in_Excel.html

If it is URL I can use TinyURL (www.TinyURL.com) to convert the lengthy URL to a much shortened version URL (a handle).

Then I hyperlink to the "tinyurl" - much less characters.

Haven't tried it with e-mail address. Same idea, I guess.

Hope this helps.

Epinn

The arguments for a HYPERLINK() worksheet function seem to be limited to 256
characters. Is there a workaround for this?

I need to embed functions like this
=HYPERLINK("mailto:[email protected]?subject=My Subject&body="&A1)
in a worksheet, and I need it to accept very long body arguments. If the
argument in this case gets much longer than 200 characters, the function
becomes a #VALUE.

Also, I can't use macros because of all the security issues the worksheet
users will encounter.

 

[Epinn]

Sorry, I missed that you were using a cell already.

There may still be a problem using concatenation of multiple cells if the characters added up to over 248??

I can't remember what I did.

Epinn

Yes, there is a limit to the number of characters (about 248?)

I put certain data/info in a cell. I can refer to the cell direct or use name definition.

Insert>Name>Define

e.g. In cell A1 I type http://www.yahoo.com and define A1 as YH.

=Hyperlink(YH) or =Hyperlink(Sheet1!A1)

This saves quite a few characters. You can use multiple cells and concatenate using &.

See if this link helps.

http://exceltips.vitalnews.com/Pages/T0463_Dynamic_Hyperlinks_in_Excel.html

If it is URL I can use TinyURL (www.TinyURL.com) to convert the lengthy URL to a much shortened version URL (a handle).

Then I hyperlink to the "tinyurl" - much less characters.

Haven't tried it with e-mail address. Same idea, I guess.

Hope this helps.

Epinn

The arguments for a HYPERLINK() worksheet function seem to be limited to 256
characters. Is there a workaround for this?

I need to embed functions like this
=HYPERLINK("mailto:[email protected]?subject=My Subject&body="&A1)
in a worksheet, and I need it to accept very long body arguments. If the
argument in this case gets much longer than 200 characters, the function
becomes a #VALUE.

Also, I can't use macros because of all the security issues the worksheet
users will encounter.

 

[Dave Booker]

Yes, even if we're using cell references, as soon as the length of the values
of the references exceeds the magic limit -- 248 or whatever it is -- the
function itself fails.

 

[Epinn]

Dave,

Any luck? I would appreciate it if you could post back the solution i.e. if there is one.

I did more research. The limit is about 255/256 which is the column width. But a cell can hold over 1,000.

We both know that =hyperlink(A1&A2) works when the sum of the characters is below the limit. It will return #VALUE if over.

To my surprise =hyperlink(A1)&hyperlink(A2) also works when below the limit. I don't have a long URL to test for the over limit condition. I put 254 characters in A1 and (also 254 characters in) A2 respectively. The formula does not return #VALUE error. It displays blue fonts and the underline i.e. the hyperlink format. I am not hopeful that this actually works. But I just want to let you know. May be you can think of something else.

Looks like FollowHyperlink method is one way but I know that's not something you want.

Epinn

Yes, even if we're using cell references, as soon as the length of the values
of the references exceeds the magic limit -- 248 or whatever it is -- the
function itself fails.