Concatenating hyperlinks longer than 255 characters?

[otvasilescu]

Hey all,

I'm trying to automate the creation of a bunch of hyperlinks that I will use regularly. This is in relation to JIRA which, you might know, can have very long hyperlinks since all arguments of a search will appear in the link itself. This is important for me since changing a few cells in excel should generate ~20 hyperlinks which would make my life a lot easier than doing each search individually in JIRA. These are advanced aggregate expression searches based on dates and I cannot achieve what I want in JIRA directly.

I'm concatenating several cells but I quickly hit the 255 character limit and I'm getting a #VALUE! error. I'm using Excel 2016. I've seen several old threads with Excel 2007 or whatever but nothing recent so I'm asking again.

Is there any workaround for this?

Note: In an older thread here, some people were saying that =HYPERLINK(A1 & A2) would work regardless of the length of characters of A1 or A2. This has never been true afaik and it definitely does not work now.

Thanks,
Ovidiu.

 

[svendiamond]

Oh I got you. Try this:

=CONCATENATE(HYPERLINK(B2&B3&B4&C5&B6&C7&B8&B9&B10),HYPERLINK(B12))

ohh wait I see. clicking on it does nothing... hmm...

 

[svendiamond]

Hmmm well this is working for me:

=B2&B3&B4&C5&B6&C7&B8&B9&B10&B12

and I can click on the link. Of course, Excel gives me a pop-up that says it can't connect but that's because I'm not on your server.

 

[otvasilescu]

Well it merges all of the cells together and gets formatted as a link.. but it's not a hyperlink. I have to manually insert link into cell afterwards (and I can't because when inserting links you're blocked by the 255 limit).

I've also tried the following:
B13 = B2&B3&B4&C5&B6&C7&B8&B9&B10&B12
B14 = HYPERLINK(B13)
This is giving me a #VALUE! link that I can click but nothing happens. If I delete "B12" from B13 formula, then B14 works as a link.

Can you please check what link you have when you hover over the hyperlink? I'm curious if it's actually the full one or if it reads just the first 255 characters.

 

[svendiamond]

Ah, yes when I hover over the link it does NOT show all the characters. Only the first 30 or so characters was a link... hmm. Strange. Also, I just want to mention that when I google this issue it comes up over and over. Mostly unsolved (except with some VBA) issues.

But anyway let me share with you part of a worksheet that I created for my company. This is all in one cell and it works just fine:

="http://webapps.homedepot.com/LORPWS2/rs/ReceivingReportBR803Service/getReceivingReportBR803Service?shipmentNumber="&i_myASN&"&displayLpn-SkuVsDisplaySku=LPNs+with+SKU&skuDisplayType=ALLSKUSONLY&poType=BOTH+RDC+AND+TF+LPNS&displayOnlyHazmat=NO&dc="&i_dcNum&"&random=451&isExport=true&fileType=exportTypeXLDataOnly"

The blue parts are named ranges that refer to two other cells. And this link is 294 characters long. I don't know why mine works and yours doesn't as it's basically the same concept. But I figured I would share it with you and maybe it would help or you would realize something that I'm not seeing. I am going to continue to look into this... because I want to help, but also because of the mystery!

 

[otvasilescu]

Well, I copy pasted that link to my excel and changed the named ranges to something else and 2 things:
1. It's not a hyperlink, it's just plain text
2. If I add another cell saying =HYPERLINK(A1) - aka the cell above, I get #VALUE! error.

So I guess the moral of the story is "it works for some people". I'll keep digging and update this thread if I find anything.

 

[otvasilescu]

I've given up on this since I've found a workaround: Google Spreadsheets doesn't seem to have this (garbage) 255 limit, so I'll be using it instead for this document.