超过Excel中的最大字符限制

How do I use more than 255 characters in Excel's CONCATENATE function? I am actually also using the CONCATENATE function within the HYPERLINK function in EXCEL. An example looks like this:

如何在Excel的CONCATENATE函数中使用超过255个字符？我实际上也在EXCEL的HYPERLINK函数中使用CONCATENATE函数。示例如下所示：

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

UPDATE: It's not issue with CONCATENATE function, but an issue with the first parameter of the HYPERLINK function. Using a string longer than 255 characters directly/indirectly (ex: =HYPERLINK(K204,"link") where K204 contains the 256-character length link) fails the HYPERLINK function

更新：这不是CONCATENATE函数的问题，而是HYPERLINK函数的第一个参数的问题。直接/间接使用长度超过255个字符的字符串（例如：= HYPERLINK（K204，“link”），其中K204包含256个字符的长度链接）HYPERLINK功能失败

I realize that I can use a URL shortener, but I am doing this for ALOT of links which would require ALOT of manual use of the URL shortener.

我意识到我可以使用URL缩短器，但我正在为很多链接做这个，这需要很多人手动使用URL缩短器。

9 个解决方案

#1

UPDATE: Because of Karls comment I revisited my answer an found out, that Excel 2007 does not seem to allow User Defined Functions to set hyperlinks anymore (quite sensibly, see my own comment in the code). So the original code (below the line) does not work in more recent versions of Excel (I haven't tested Excel 2010 but I assume the result is the same). For historical reasons I do not delete the old code (an editor might think otherwise -- feel free to edit/ delete accordingly).

更新：由于Karls的评论，我重新审视了我的答案，Excel 2007似乎不再允许用户定义函数设置超链接（非常明智，请参阅我自己在代码中的注释）。所以原始代码（在行下面）在最新版本的Excel中不起作用（我没有测试Excel 2010，但我假设结果是相同的）。由于历史原因，我不删除旧代码（编辑可能会认为不同 - 随意编辑/删除）。

So what remains is to set long hyperlinks programatically, e.g.

那么剩下的就是以编程方式设置长超链接，例如，

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 follows does not work in Excel 2010 anymore; see my comment above

以下内容不再适用于Excel 2010;看我上面的评论

The "copy the hyperlink from Word and paste into Excel" got me thinking. So obviously the limit is both in the built-in HYPERLINK-function and in the dialog-window 'edit hyperlink'. On the other hand it should be -- and actually is -- possible to set longer hyperlinks via VBA.

“从Word复制超链接并粘贴到Excel中”让我思考。显然，限制是在内置的HYPERLINK函数和对话框窗口的“编辑超链接”中。另一方面，应该 - 实际上 - 可以通过VBA设置更长的超链接。

This code does not work in Excel 2010 anymore

此代码不再适用于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 normal Excel-function, but be sure to add the current cell as first parameter (i.e. the following formula is inserted in cell A1)

用作普通的Excel函数，但一定要将当前单元格添加为第一个参数（即在单元格A1中插入以下公式）

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

You can neither pull the formula down nor copy it to another cell. If you do that you have to let the formula be recalculated (neither ALT-CTRL-F9 nor ALT-CTRL-SHIFT-F9 as force recalculate seem to work) so go into each cell, press F2 to activate it and finish with Return.

您既不能拉下公式也不能将其复制到另一个单元格。如果你这样做，你必须重新计算公式（ALT-CTRL-F9和ALT-CTRL-SHIFT-F9都不重新计算似乎有效）所以进入每个单元格，按F2激活它并完成返回。

I hope I am not helping you to screw up too many Excel-Workbooks.

我希望我不会帮你搞砸过多的Excel工作簿。

It is probably safer to write an VBA that is explicitly started that iterates through a list and writes to hyperlinks. That way they can reused and there are no functions.

编写显式启动的VBA可能更安全，该VBA遍历列表并写入超链接。这样他们就可以重复使用而且没有任何功能。

Regards Andreas

关心安德烈亚斯

#2

You might be out of luck. It seems that the character limit for hyperlinks in Excel is 256 as pointed out here. If you test it out yourself (I have Excel 2007, too), =HYPERLINK(REPT("a",255)) works and =HYPERLINK(REPT("a",256)) does not and throws a #VALUE! error.

你可能运气不好。似乎Excel中的超链接的字符限制是256，如此处所指出的。如果你自己测试它（我也有Excel 2007），= HYPERLINK（REPT（“a”，255））工作，= HYPERLINK（REPT（“a”，256））没有，并抛出#VALUE！错误。

#3

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

我有Excel 2007，我尝试在A1中创建一个包含300个字符的单元格，在B1中创建另外300个不同字符的单元格。

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

然后我做了C1 = CONCATENATE（A1，B1）。

I can see all of the characters from both cells. Nothing is missing or truncated and no errors were received. It looks good to me.

我可以看到两个细胞中的所有角色。没有遗漏或截断，也没有收到任何错误。这对我来说很好看。

What makes you think that the concatenate is failing? Are you having trouble seeing your results? If your cell contains more than 1,024 characters only the first 1,024 are displayed in the cell. However they are still there and if you copy and paste them all of the characters will be copied.

是什么让你认为连接失败了？您是否在查看结果时遇到问题？如果您的单元格包含超过1,024个字符，则单元格中仅显示前1,024个字符。但是它们仍然在那里，如果你复制并粘贴它们，所有的字符都将被复制。

Edit: Now that you have editted your question I realize the problem is with HYPERLINK and not CONCATENATE.

编辑：既然你已经编辑了你的问题，我意识到问题出在HYPERLINK而不是CONCATENATE。

The only way to get around the 255 character limit of HYPERLINK formula in Excel is to copy a hyperlink from Word and paste it into a cell in Excel. Then it can be super long. I know this is an unreasonable manual process if you have a lot of links but it seems the only way to get it into an Excel spreadsheet and yet still have it be a hyperlink that you can click on and be redirected. If you don't need it to act like a hyperlink then I would suggest rewriting your queries to return the hyperlink as its own text field and then it will be fine.

在Excel中绕过HYPERLINK公式的255个字符限制的唯一方法是从Word复制超链接并将其粘贴到Excel中的单元格中。然后它可以超长。我知道如果你有很多链接这是一个不合理的手动过程，但它似乎是将它放入Excel电子表格的唯一方法，但它仍然是一个超链接，你可以点击并重定向。如果您不需要它像超链接那样，那么我建议重写您的查询以将超链接作为自己的文本字段返回，然后就可以了。

#4

Here's some VBA which uses bitly.com to shorten a URL. It is based on the bitly API documentation.

这是一些使用bitly.com缩短URL的VBA。它基于有点的API文档。

Create a free account on bitly.
在bitly上创建一个免费帐户。
Valid email address with bitly.
有效的电子邮件地址。
Get access token from bitly.
从bitly获取访问令牌。
Substitute the access token in the VBA code below where it says MY_TOKEN.
将下面的VBA代码中的访问令牌替换为MY_TOKEN。
Copy and paste the code in Excel's VBA.
将代码复制并粘贴到Excel的VBA中。
In a cell, write the following '=Hyperlink(GetURL("some really long URL"))' without single quote ' marks. Note: Instead of passing a string to GetURL(), pass a reference to a cell which has a URL in it as text.
在单元格中，写下以下'=超链接（GetURL（“一些非常长的URL”））''没有单引号'标记。注意：不是将字符串传递给GetURL（），而是将引用传递给其中包含URL的单元格作为文本。

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

#5

Dunno if my answer is still useful but I had the same issue couple of days ago, the best way and proved way to do a workable hyperlink that exceeds a 255 char limit is to first split it, with CONCATENATE(), and use the cell with CONCATENATE() function in VBA.

Dunno如果我的答案仍然有用，但几天前我遇到了同样的问题，那么做一个超过255个字符限制的可行超链接的最佳方法和证明方法是先用CONCATENATE（）拆分它，并使用单元格在VBA中使用CONCATENATE（）函数。

For me it looks like:

对我来说，它看起来像：

A1 = LinkPart1
A2 = LinkPart2
A3 = LinkPart3

A5 = CONCATENATE( A1; A2; A3 )

VBA Code you need to link with A5:

您需要与A5链接的VBA代码：

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

#6

Instead of writing

而不是写作

=CONCATENATE("Toto";"Tata")

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

将Toto放入单元格Z1（例如）和单元格Z2中的Tata并写入

=CONCATENATE(Z1;Z2)

#7

Guys I think a URL Shortening VBA will help you. Here is one which I found today. It works like a charm: http://www.jpsoftwaretech.com/shorten-urls-with-bit-ly-web-api-and-vba/

各位我认为URL缩短VBA会对你有所帮助。这是我今天发现的一个。它就像一个魅力：http：//www.jpsoftwaretech.com/shorten-urls-with-bit-ly-web-api-and-vba/

#8

The Hyperlink function has a hard limit that can't be overstaped. I had a similar problem and I simply imported the Excel sheet into Open Office Calc and voila - everything worked instantly and the hyperlink that was to long previously can be now as long as I wanted it to be.

超链接功能具有无法超载的硬限制。我有一个类似的问题，我只是将Excel表格导入Open Office Calc并且瞧 - 一切都立即起作用，而且以前很久的超链接现在只要我想要它就可以了。

#9

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

您可以使用VBA Shell（）例程来执行浏览器，并在通过Shell（）调用传递的命令行上将URL传递给它。因此，URL可以是shell机制支持的任何长度。

Furthermore you can get this URL from any cell value by having the user double-click that cell. This value can be constructed from many cells via a single CONCATENATE() function call! That's right: just a single call. The CONCATENATE() will take a large number of parameters and will create a string well-bigger than 255 characters. You don't need to laboriously join many separate concatenations or use loads of "builder" cells. One will do!

此外，您可以通过让用户双击该单元格从任何单元格值获取此URL。可以通过单个CONCATENATE（）函数调用从许多单元格构造此值！没错：只需一个电话。 CONCATENATE（）将获取大量参数，并将创建一个大于255个字符的字符串。您不需要费力地加入许多单独的连接或使用大量的“构建器”单元格。一个会做！

The macro needs to be created by opening the VIEW CODE option when you right-click the tab at the bottom of the worksheet. Then write the following phenomenally short, simple and painless bit of code:

当您右键单击工作表底部的选项卡时，需要通过打开VIEW 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

Note that "Browser" is a named cell that should contain the unquoted path of your browser, be that IE, Opera, Mozilla or Chrome. You have to name the cell yourself, or change the macro to have a hard cell reference like "A2". And of course, that cell value must be a valid browser path!

请注意，“浏览器”是一个命名单元格，应包含浏览器的未加引号的路径，即IE，Opera，Mozilla或Chrome。您必须自己命名单元格，或更改宏以具有像“A2”这样的硬单元格引用。当然，该单元格值必须是有效的浏览器路径！

Once you have all of this in place, you can double-click ANY cell that has a value starting with the text "HTTP://" and Excel will open the browser with that full value, no-matter how long it is. All you then need is to build your string in that cell and perhaps format it colour/font-wise to make it obvious that it is a hyperlink cell to be double-clicked. A textual hint nearby may also be in order!

完成所有这些后，您可以双击任何具有以“HTTP：//”开头的值的单元格，Excel将使用该完整值打开浏览器，无论它有多长。您需要的只是在该单元格中构建您的字符串，并可能将其格式化为颜色/字体，以明确它是一个双击的超链接单元格。附近的文字提示也可能是有序的！

Incidentally, an alternative to the Shell() line in the macro is:

顺便提一下，宏中Shell（）行的替代方法是：

ThisWorkbook.FollowHyperlink Address:=Target.Value

Whilst this will also process URLs bigger than 255 characters, I found that this FollowHyperlink() function causes the URL to be sent TWICE. Once by the Excel function itself (presumably to test it) and then again by the default browser that Excel opens! This may not be desirable (and wasn't in my case). This is why I ended up using the Shell() function instead.

虽然这也会处理大于255个字符的URL，但我发现这个FollowHyperlink（）函数会导致URL被发送TWICE。一旦通过Excel函数本身（可能是为了测试它），然后再由Excel打开的默认浏览器！这可能是不可取的（并不是我的情况）。这就是我最终使用Shell（）函数的原因。

#1