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]

I have a similar situation at work where I have hyperlinks that are super long and the way I do it is like this:

="http://webapps.myworkplace.com/whateverwhatever"&A2&"displayType=ShipmentBlahBlahDateIs"&A3&"etcetckeepgoing"&A4&"blahblah"

Where A2, A3, and A4 have variables that are needed as part of the hyperlink (in my case, numbers for dates, times, and shipment IDs. I have some hyperlinks that end up being over 400 characters and they work.

 

[otvasilescu]

Maybe it's something your IT department did? Does anyone have a clue?
Are you using excel 2016?

 

[svendiamond]

No its not my IT department, yes Excel 2016 and it should work for you too. Can you give an example of one of your links? Do they use other cells to create a long link? You should just be able to use the ampersand...

 

[otvasilescu]

Example of link would be:

http://jira:1234/issues/?jql=project%20%3D%20XYZ%20AND%20(cf%5B10800%5D%20in%20workLogged(%22after%202016-07-18%20before%202016-07-22%20ingroup%20Code-CORE%22))%20and%20cf%5B10800%5D%20in%20aggregateExpression(%22Total%20time%20logged%22%2C%20%22timespent.sum()%22%20)

<tbody>
</tbody>

I concatenated it up until this part (which works as a link):
http://jira:1234/issues/?jql=projec...-07-18 before 2016-07-29 ingroup CODE-CORE"))
The concatenated formula is:
=HYPERLINK(CONCATENATE(B5,B6,B7,TEXT(B8, "yyyy-mm-dd"),B9,TEXT(B10, "yyyy-mm-dd"),B11,B12,B13))

I can obviously make it prettier / simpler later but the idea is that if I want to add the 2nd part of the link, the Hyperlink stops working (I add this as a single cell in the concatenate formula above):
%20and%20cf%5B10800%5D%20in%20aggregateExpression(%22Total%20time%20logged%22%2C%20%22timespent.sum()%22%20)

Cheers,
Ovidiu.

 

[svendiamond]

It worked for me... I typed your exact formula, made up some data to go into those cells and everything was fine.

I just added ",B14" onto the end of yours (as I put that long string into B14) and it worked fine?

 

[otvasilescu]

OK, any idea what I'm doing wrong?

 

[otvasilescu]

OK, I can't edit. Just wanted to mention that I added a "friendly name" to the hyperlink as well, thinking the full string might be too long for a cell. Still get a #VALUE! error.

 

[svendiamond]

I think excel is getting confused because within the text in B12 there is a part that says "sum()" -- but if you just change it to this it works:

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

 

[otvasilescu]

But this way B12 is outside of the Hyperlink formula and will not be taken into account when opening the link...
I did this and it looks good but clicking on it doesn't work. I can click it like any link but absolutely nothing happens (not even an error, considering the port / project name etc. are dummies). Again, the same without B12 works.

Just to be clear, if I trim it down to exactly 255 characters, it works. If i go just 1 character above that, it stops working. I tried with and without sum() and it doesn't matter.