Exceeding Char's maximum limit in Excel

Question

How to use more than 255 characters in Excel CONCATENATE function? I also use the CONCATENATE function in the HYPERLINK function in EXCEL. An example is as follows:

=HYPERLINK(CONCATENATE("http://www.google/com/morethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255chars","morethan255chars morethan255charsmorethan255charsmorethan255charsmorethan25"),"link"); 

UPDATE: it does not return the CONCATENATE function, but there is a problem with the first parameter of the HYPERLINK function. Using a string longer than 255 characters directly / indirectly (ex: = HYPERLINK (K204, "link"), where K204 contains a 256-character link) the HYPERLINK function does not work

I understand that I can use an abbreviated URL, but I am doing this for ALOT links that require ALOT to manually use the URL shortener.

Answer 1

UPDATE: due to Karls comment, I revised my answer, found out that Excel 2007 does not seem to allow user-defined functions to set hyperlinks anymore (quite reasonably, see my own comment in the code). Thus, the source code (below the line) does not work in later versions of Excel (I have not tested Excel 2010, but I assume that the result is the same). For historical reasons, I do not delete the old code (the editor may think differently - feel free to edit / delete accordingly).

So it remains to set long hyperlinks programmatically, for example

 Sub insertVeryLongHyperlink() Dim curCell As Range Dim longHyperlink As String Set curCell = Range("A1") ' or use any cell-reference longHyperlink = "http://www.veryLongURL.com/abcde" ' Or a Cell reference like [C1] curCell.Hyperlinks.Add Anchor:=curCell, _ Address:=longHyperlink, _ SubAddress:="", _ ScreenTip:=" - Click here to follow the hyperlink", _ TextToDisplay:="Long Hyperlink" End Sub 

---

What further does not work in Excel 2010; see my comment above

"Copy the hyperlink from Word and paste into Excel" made me think. Obviously, the limit is both in the built-in HYPERLINK function and in the "edit hyperlink" dialog box. On the other hand, it should be - and in fact - it is possible to set longer hyperlinks through VBA.

This code no longer works in Excel 2010

 Function myHyperlink(cell As Range, _ hyperlinkAddress As String, _ Optional TextToDisplay As Variant, _ Optional ScreenTip As Variant) ' Inserts a Hyperlink ' at the position cell (this should be the position where the UDF is used, ' since the return value of the UDF is = TextToDisplay) ' with the hyperlinkAddress ' optional TextToDisplay ' optional ScreenTip ' ####################################### ' Warning Warning Warning Warning Warning ' ####################################### ' 1) Since it is really bad practice to have a function perform procedural ' tasks, you should not do this. ' 2) You have no garantee, the link is updated when the value hyperlinkAddress changes ' USE AT YOUR ONE RISK AND ONLY IN CASE OF EMERGENCIES :-) ' If more than one cell is selected as target range, ' use the top left cell Set cell = cell.Resize(1, 1) If IsMissing(TextToDisplay) Then TextToDisplay = hyperlinkAddress End If If IsMissing(ScreenTip) Then ScreenTip = hyperlinkAddress & " - Click here to follow the hyperlink" End If cell.Hyperlinks.Add Anchor:=ActiveCell, _ Address:=hyperlinkAddress, _ SubAddress:="", _ ScreenTip:=ScreenTip, _ TextToDisplay:=TextToDisplay ' There doesn't seem to be another way to set TextToDisplay myHyperlink = TextToDisplay End Function 

Use as a regular Excel function, but be sure to add the current cell as the first parameter (i.e. the following formula is inserted in cell A1)

 =myHyperlink(A1,B1) =myHyperlink(A1,B1,"TextToDisplay", "ScreenTip") 

You can neither drag the formula, nor copy it to another cell. If you do this, you will have to recalculate the formula (neither ALT-CTRL-F9 nor ALT-CTRL-SHIFT-F9 work as a force recalculation), so go to each cell, press F2 to activate it and end with Return .

I hope I do not help you mess up too many Excel workbooks.

It is probably safer to write a VBA that is explicitly running, iterating through the list and writing to hyperlinks. Thus, they can be reused, but no functions.

Relations Andreas

Answer 2

I have Excel 2007, and I tried to create a cell with 300 characters in A1, and another with 300 different characters in B1.

Then I did C1 = CONCATENATE(A1, B1) .

I see all the characters from both cells. Nothing is missing or truncated, and no errors were received. I feel good.

What makes you think concatenation is failing? Are you having problems with the results? If your cell contains more than 1024 characters, only the first 1024 are displayed in the cell. However, they still exist, and if you copy and paste them, all characters will be copied.

Edit: Now that you have changed your question, I understand that the problem is with HYPERLINK , not CONCATENATE .

The only way to get around the 255 character limit in the HYPERLINK formula in Excel is to copy the hyperlink from Word and paste it into the cell in Excel. Then it can be very long. I know this is an unreasonable manual process if you have many links, but this is the only way to get it in an Excel spreadsheet and still have a hyperlink that you can click and redirect. If you do not need it to act as a hyperlink, I would suggest rewriting your requests in order to return the hyperlink as its own text field, and then everything will be fine.

Answer 3

You may be out of luck. The character limit for hyperlinks in Excel seems to be 256, as indicated here . If you check it yourself (I also have Excel 2007), =HYPERLINK(REPT("a",255)) works and =HYPERLINK(REPT("a",256)) does not and throws #VALUE! mistake.

Answer 4

Here is some VBA that bitly.com uses to shorten the URL. It is based on API bitmap documentation .

• Create a free bitly account .
• Valid email address with bitly .
• Get access token from bitly .
• Substitute the access token in the VBA code below where MY_TOKEN is indicated.
• Copy and paste the code into Excel VBA.
• In the cell, write the following link: = Hyperlink (GetURL ("some really long URL")) "Without a single quote." Note. Instead of passing the string to GetURL (), pass the link to the cell with the URL in it as text.

 Public Function GetURL(longUrl As String) As String Dim xml As Object longUrl = URLEncode(longUrl) Set xml = CreateObject("MSXML2.XMLHTTP.6.0") xml.Open "GET", "https://api-ssl.bitly.com/v3/shorten?format=xml&access_token=MY_TOKEN=" & longUrl, False xml.Send GetURL = xml.responsetext head = InStr(GetURL, "<url>") + 5 tail = InStr(GetURL, "</url>") GetURL = Mid(GetURL, head, tail - head) End Function Function URLEncode(ByVal Text As String) As String Dim i As Integer Dim acode As Integer Dim char As String URLEncode = Text For i = Len(URLEncode) To 1 Step -1 acode = Asc(Mid$(URLEncode, i, 1)) Select Case acode Case 48 To 57, 65 To 90, 97 To 122 ' don't touch alphanumeric chars Case 32 ' replace space with "+" Mid$(URLEncode, i, 1) = "+" Case Else ' replace punctuation chars with "%hex" URLEncode = Left$(URLEncode, i - 1) & "%" & Hex$(acode) & Mid$(URLEncode, i + 1) End Select Next End Function 

Answer 5

I don’t know if my answer is still useful, but I had the same problem a couple of days ago, the best way and a proven way to make a working hyperlink that exceeds the char limit of 255 is to split it first, with CONCATENATE() , and use cell with the CONCATENATE() function in VBA .

For me it looks like this:

 A1 = LinkPart1 A2 = LinkPart2 A3 = LinkPart3 A5 = CONCATENATE( A1; A2; A3 ) 

VBA Code to be associated with A5 :

 Sub insertVeryLongHyperlink() Dim curCell As Range Dim longHyperlink As String Set curCell = Range("A7") ' or use any cell-reference longHyperlink = [A5] curCell.Hyperlinks.Add Anchor:=curCell, _ Address:=longHyperlink, _ SubAddress:="", _ ScreenTip:=" - Click here to follow the hyperlink", _ TextToDisplay:="Click Here" End Sub 

Answer 6

Instead of recording

 =CONCATENATE("Toto";"Tata") 

Put Toto in cell Z1 (for example) and Tata in cell Z2 and write

 =CONCATENATE(Z1;Z2) 

Answer 7

Guys, I think the Shortening VBA URL will help you. Here is what I found today. Works great: http://www.jpsoftwaretech.com/shorten-urls-with-bit-ly-web-api-and-vba/

Answer 8

The hyperlink function has a hard limit that cannot be underestimated. I had a similar problem, and I just imported an Excel worksheet into Open Office Calc and voila - everything worked instantly, and the hyperlink that was there a long time ago, maybe as long as I wanted it.

Answer 9

You can use the VBA Shell () routine to execute the browser and pass it the URL on the command line passed through the Shell () call. Thus, the URL can be any length supported by the shell engine.

In addition, you can get this URL from any cell value if the user double-clicked this cell. This value can be built from many cells through a single call to the CONCATENATE () function! That's right: just one call. CONCATENATE () will take a large number of parameters and create a string that will contain more than 255 characters. You do not have to join many separate concatenations with difficulty or use many "builder" cells. One will do!

You must create a macro by opening the VIEW CODE option when you right-click the tab at the bottom of the table. Then write the following phenomenally short, simple, and painless bit of code:

 Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Selection.Count = 1 Then If Left(Target.Value, 7) = "HTTP://" Then Cancel = True Shell ("""" + Range("Browser").Value + """" + " " + """" + Target.Value + """") End If End If End Sub 

Please note that the “Browser” is a named cell, which should contain the unspecified path of your browser, be it IE, Opera, Mozilla or Chrome. You must name the cell yourself or modify the macro to have a reference to a solid cell such as "A2". And, of course, this cell value must be valid for the browser!

Once you have all this in place, you can double-click on any cell that has a value beginning with the text "HTTP: //" and Excel will open a browser with this full value, no matter how long it takes. All you need to do is build your line in this cell and possibly format its color / font so that it is obvious that this is a hyperlink that you need to double-click. A text hint nearby may also be ok!

By the way, an alternative to the Shell () line in the macro:

 ThisWorkbook.FollowHyperlink Address:=Target.Value 

While this will process URLs greater than 255 characters, I found that this FollowHyperlink () function causes the TWICE URL to be sent. Once using the Excel function itself (presumably to test it), and then again the default browser that opens Excel! This may be undesirable (and was not in my case). This is why I ended up using the Shell () function.