string Excel：使用超过 255 个字符的公式

Excel: Use formula longer that 255 characters

提问by Hip Hip Array

I have the following formula in excel

我在excel中有以下公式

=CONCATENATE("insert into #UpdateData (mondayopenhour, mondayopenmin,mondayclosehour, mondayclosemin,tuesdayopenhour, tuesdayopenmin,tuesdayclosehour, tuesdayclosemin,wednesdayopenhour, wednesdayopenmin, wednesdayclosehour, wednesdayclosemin,thursdayopenhour, thursdayopenmin,thursdayclosehour, thursdayclosemin, fridayopenhour, fridayopenmin, fridayclosehour, fridayclosemin, saturdayopenhour, saturdayopenmin, saturdayclosehour, saturdayclosemin, sundayopenhour, sundayopenmin, sundayclosehour, sundayclosemin values ('",TRIM(A2),"',",MID(B2,1,2),",",MID(B2,3,2),",",MID(C2,1,2),",",MID(C2,3,2),",",MID(D2,1,2),",",MID(D2,3,2),",",(MID(E2,1,2),",",MID(E2,3,2),",",MID(F2,1,2),",",MID(F2,3,2),",",MID(G2,1,2),",",MID(G2,3,2),",",MID(H2,1,2),",",MID(H2,3,2),",",MID(I2,1,2),",",MID(I2,3,2),",",MID(J2,1,2),",",MID(J2,3,2),",",MID(K2,1,2),",",MID(K2,3,2),",",MID(L2,1,2),",",MID(L2,3,2),",",MID(M2,1,2),",",MID(M2,3,2),",",MID(N2,1,2),",",MID(N2,3,2),",",MID(O2,1,2),",",MID(O2,3,2),")")

When I try run it i get the following error

当我尝试运行它时，出现以下错误

Text values in formulas are limited to 255 characters. To create text values longer than 255 characters in a formula, use the CONCATENATE function or the concatenation operator (&).

公式中的文本值限制为 255 个字符。要在公式中创建长度超过 255 个字符的文本值，请使用 CONCATENATE 函数或串联运算符 (&)。

I have been looking this up online but have got no real solutions for this! does anybody know how to solve this?

我一直在网上查这个，但没有真正的解决方案！有谁知道如何解决这个问题？

采纳答案by Jook

Now this is one messy function. You have to seperate your input to CONCATENATE.

现在这是一个凌乱的功能。您必须将输入分开到CONCATENATE.

Right now you have there only one very big string starting on "insert ..." ending on "....MID(O2,3,2),")" - or at least, that is where I think this ends, because of all the "," it's really tough to step through this.

现在你只有一个非常大的字符串，以“insert ...”开头，以“....MID(O2,3,2),”)”结尾——或者至少，我认为这就是结束的地方，因为所有的“，”很难跨过这一步。

Here is how you should use CONCATENATE:

以下是您应该如何使用CONCATENATE：

=CONCATENATE("insert into #UpdateData (mondayopenhour,","mondayopenmin,")

You can extend this, so you won't have a single string which is longer than 255 characters.

您可以扩展它，因此您不会有一个长度超过 255 个字符的字符串。

edit: by the way - your current string has about 972 characters.

编辑：顺便说一下-您当前的字符串大约有 972 个字符。

you can ease the seperation by using this formula:

您可以使用以下公式简化分离：

=MID("insert into #UpdateData (mondayopenhour, mondayopenmin,mondayclosehour, mondayclosemin,tuesdayopenhour, tuesdayopenmin,tuesdayclosehour, tuesdayclosemin,wednesdayopenhour, wednesdayopenmin, wednesdayclosehour, wednesdayclosemin,thursdayopenhour, thursdayopenmin,thursdayclosehour, thursdayclosemin, fridayopenhour, fridayopenmin, fridayclosehour, fridayclosemin, saturdayopenhour, saturdayopenmin, saturdayclosehour, saturdayclosemin, sundayopenhour, sundayopenmin, sundayclosehour, sundayclosemin values ('",TRIM(A2),"',",MID(B2,1,2),",",MID(B2,3,2),",",MID(C2,1,2),",",MID(C2,3,2),",",MID(D2,1,2),",",MID(D2,3,2),",",(MID(E2,1,2),",",MID(E2,3,2),",",MID(F2,1,2),",",MID(F2,3,2),",",MID(G2,1,2),",",MID(G2,3,2),",",MID(H2,1,2),",",MID(H2,3,2),",",MID(I2,1,2),",",MID(I2,3,2),",",MID(J2,1,2),",",MID(J2,3,2),",",MID(K2,1,2),",",MID(K2,3,2),",",MID(L2,1,2),",",MID(L2,3,2),",",MID(M2,1,2),",",MID(M2,3,2),",",MID(N2,1,2),",",MID(N2,3,2),",",MID(O2,1,2),",",MID(O2,3,2),")",2,255)

回答by kirsti

I just hit this problem too... but its not really a problem. You can only have 255 chars in a cell, but unlimited in a formula. Your formula will result in an error, so excel is reading it as text rather than a formula. Find your error and your formula will work.

我也遇到了这个问题……但这不是真正的问题。一个单元格中只能有 255 个字符，但在公式中没有限制。您的公式将导致错误，因此 excel 将其作为文本而不是公式读取。找出你的错误，你的公式就会起作用。

回答by Nik

Well as for my solution for the same problem, those long text I put it on a separate cell and not part of my formula itself. Example:

至于我对同一问题的解决方案，那些长文本我把它放在一个单独的单元格上，而不是我的公式本身的一部分。例子：

=CONCATENATE("INSERT INTO CAS_ACD (ACD_NUMBER, WAGON_WHEEL_INDEX, WAGON_WHEEL_COUNT, WAGON_WHEEL_COUNT_MAX, WAIT_TIME, MAX_WAIT_TIME,
MON_TIME, TUE_TIME, WED_TIME, THU_TIME, FRI_TIME, SAT_TIME, SUN_TIME, DATE_CLOSED1,
DATE_CLOSED2, DATE_CLOSED3, DATE_CLOSED4, DATE_CLOSED5, DATE_CLOSED6, DATE_CLOSED7, DATE_CLOSED8,
DATE_CLOSED9, DATE_CLOSED10, DATE_CLOSED11, DATE_CLOSED12, DATE_CLOSED13, DATE_CLOSED14,
DATE_CLOSED15, DATE_CLOSED16, DATE_CLOSED17, DATE_CLOSED18, DATE_CLOSED19, DATE_CLOSED20,
CTI_SERVER_ADDR, PROGRAM_NUM, VERSION_NUM, COMMENTS, TOD_VOICE_DIR, TOD_VS, ACD_NAME) 
VALUES ("B2, C2,");")

Instead, I store this to one of the cell A2

相反，我将其存储到单元格 A2 之一

Cell A2 now contains=
INSERT INTO CAS_ACD (ACD_NUMBER, WAGON_WHEEL_INDEX, WAGON_WHEEL_COUNT, WAGON_WHEEL_COUNT_MAX, WAIT_TIME, MAX_WAIT_TIME,
MON_TIME, TUE_TIME, WED_TIME, THU_TIME, FRI_TIME, SAT_TIME, SUN_TIME, DATE_CLOSED1,
DATE_CLOSED2, DATE_CLOSED3, DATE_CLOSED4, DATE_CLOSED5, DATE_CLOSED6, DATE_CLOSED7, DATE_CLOSED8,
DATE_CLOSED9, DATE_CLOSED10, DATE_CLOSED11, DATE_CLOSED12, DATE_CLOSED13, DATE_CLOSED14,
DATE_CLOSED15, DATE_CLOSED16, DATE_CLOSED17, DATE_CLOSED18, DATE_CLOSED19, DATE_CLOSED20,
CTI_SERVER_ADDR, PROGRAM_NUM, VERSION_NUM, COMMENTS, TOD_VOICE_DIR, TOD_VS, ACD_NAME) 
VALUES ('

So my formula now is

所以我现在的公式是

=CONCATENATE( A2, B2, C2)

回答by Christian Sauer

The easisest way to solve this would be to use some cells to build separate elements of the string and merge them in one cell using concetanate. It provides more room for error solving - maybe your statement has errors, but they would be all but invisible doe to their length.

解决这个问题的最简单方法是使用一些单元格来构建字符串的单独元素，并使用 cetanate 将它们合并到一个单元格中。它为解决错误提供了更多空间——也许你的语句有错误，但它们的长度几乎是看不见的。

回答by Locom

I had this problem with "over 255 char" and solved it without CONCATENATE easily:

我遇到了“超过 255 个字符”的问题，并在没有 CONCATENATE 的情况下轻松解决了这个问题：

I put into cell T1the long string of Values. Then my Formula was as below:

我将T1一长串值放入单元格中。然后我的公式如下：

="INSERT INTO sql_table_name ("&T1&") VALUES ('"&A40&"','"&B40&"','"&C40&"','"&D40&"','"&E40&"','"&F40&"','"&G40&"','"&H40&"','"&I40&"','"&J40&"','"&K40&"','"&L40&"','"&M40&"','"&N40&"','"&O40&"','"&P40&"','"&Q40&"','"&R40&"','"&S40&"');"