超过Excel中的最大值限制 [英] Exceeding Max Char Limit in Excel

问题描述

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

  = HYPERLINK（CONCATENATE（CONCATENATE（CONCATENATE（CONCATENATE）（http://www.google/com/morethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255charsmorethan255chars more），link）; 
  

更新：CONCATENATE函数没有问题，但是HYPERLINK函数的第一个参数是一个问题。使用长度超过255个字符的字符串（例如：= HYPERLINK（K204，link），其中K204包含256个字符长度的链接）失败HYPERLINK函数

我意识到我可以使用一个URL缩短器，但我正在为ALOT链接，这将需要ALOT手动使用URL缩短器。

解决方案更新：由于卡尔斯的评论，我重新找回了我的答案，Excel 2007似乎不允许用户定义的函数设置超链接（非常明智地看到我自己的代码中的注释）。所以原来的代码（下面的行）在更新版本的Excel中不起作用（我没有测试Excel 2010，但我认为结果是一样的）。由于历史原因，我不删除旧的代码（编辑可能会认为其他情况可以随意编辑/删除相应的）。

那么剩下的是设置长超链接例如

  Sub insertVeryLongHyperlink（）
 
 Dim curCell As Range 
 Dim longHyperlink As String 
 
设置curCell = Range（A1）'或使用任何单元格参考
 longHyperlink =http://www.veryLongURL.com/abcde或单元格引用，如[C1] 
 
 curCell.Hyperlinks.Add Anchor：= curCell，_ 
地址：= longHyperlink，_ 
 SubAddress：=，_ 
 ScreenTip：=  - 点击此处按照超链接，_ 
 TextToDisplay：=Long Hyperlink
 
 End Sub 
  

---

以下内容在Excel 2010中不起作用;看到我上面的评论

从Word复制超链接并粘贴到Excel让我想起了。显然这个限制是在内置的HYPERLINK功能和对话窗口'编辑超链接'。另一方面，它应该是 - 实际上可以通过VBA设置更长的超链接。

此代码在Excel 2010中不起作用

 函数myHyperlink（cell As Range，_ 
 hyperlinkAddress As String，_ 
可选TextToDisplay作为变体，_ 
可选的ScreenTip作为变体）
 
'在位置单元格中插入超链接
'（这应该是使用UDF的位置，
 '因为UDF的返回值是= TextToDisplay）
'与超链接地址
'可选TextToDisplay 
'可选ScreenTip 
 
'####### ############################### 
'警告警告警告警告警告
'### ################################### 
 
'1）由于它是真的b广告实践有一个功能执行程序
'任务，你不应该这样做。 
'2）您没有保证金，当超链接地址值更改
 
'时，该链接将被更新。只有在您的一个风险中使用，并且仅适用于紧急情况:-) 
 
 
'如果选择多个单元格作为目标范围，
'使用左上角的单元格
设置单元格=单元格.Resize（1，1）
 
如果IsMissing（TextToDisplay）然后
 TextToDisplay = hyperlinkAddress 
如果
 
如果IsMissing（ScreenTip）然后
 ScreenTip = hyperlinkAddress&  - 点击此处按照超链接
 End If 
 
 cell.Hyperlinks.Add Anchor：= ActiveCell，_ 
地址：= hyperlinkAddress，_ 
 SubAddress： =，_ 
 ScreenTip：= ScreenTip，_ 
 TextToDisplay：= TextToDisplay 
 
'似乎没有另一种方式设置TextToDisplay 
 myHyperlink = TextToDisplay 
 
结束函数
  

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

  = myHyperlink（A1，B1 ）
 = myHyperlink（A1，B1，TextToDisplay，ScreenTip）
  

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

我希望我不会帮你解决太多的Excel工作簿。

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

关于
Andreas

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:

=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

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.

解决方案

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).

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

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.

This code does not work in Excel 2010 anymore

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)

=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.

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

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.

Regards Andreas

这篇关于超过Excel中的最大值限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！