String too long when using forumla - #value

[dragonzrawr]

I've been trying to get this to work for a while now but having no luck.

I've a spreadsheet which I'm using to create a hyperlink to an email. The idea is that users can click the 'email' button and it will take them to their email, and auto-fill the 'to', 'cc', 'subject' and a couple of lines of text in the body (stuff they need to know for individual emails, the main body of the text is being created through QuickParts in Outlook).

Most of them are working, but some are coming up with '#value' where the string seems to be too long for excel 2013 to cope with.

The formula is here:
=HYPERLINK("mailto:"&C1&"?cc="&E1&"&subject="&A1&"&body="&F1, "Info Request")
Some of the emails have 4 or 5 addresses in them, separated by ';'.

Is there any way to expand the number of characters that excel will allow returned in that formula? Or is there another way of writing the formula that wont' return an error?

BTW - it doesn't seem to be limited to 255 characters. I've used =len to find the characters in the string, and as long as they are under 290'ish it seems to be fine. I've a couple over 300 and they are fine too!

Any and all help muchly appreciated!

[:) Sixthsense :)]

#value error is not related with cell character limit.

It will populate when you do calculation with text data.

[dragonzrawr]

Hi Sixthsense - thanks for your reply, but am not sure I know what you mean?
All the cells are set to text...I can't upload a pic for some reason to show you what's happening, but if I type 'test' into column F, it works fine. If I type 'test request', it comes up with #value

[:) Sixthsense :)]

It's working fine for me for both text

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[dragonzrawr]

Thanks so much for your help ;-)

I've attached a sample workbook. In column 'F' , it's currently working. however, if you type 'test data', it breaks.

[:) Sixthsense :)]

After further testing the Link Location argument of Hyperlink() function accepts maximum of 255 charter length only.

It's resulting #VALUE! when the location text length is >255

[dragonzrawr]

That's what I thought - I just didn't know how to phrase it! :D
Is there anything I can do, or do I just have to live with it? I tried concatenate and a couple of other things but everything seemed to result in the same error.
Thanks again!

[romperstomper]

You could use code in a Worksheet_Change event to add an actual hyperlink to the column instead of using the HYPERLINK function.