Excel:使用超过 255 个字符的公式 [英] Excel: Use formula longer that 255 characters

问题描述

我在excel中有以下公式

I have the following formula in 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

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

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

我一直在网上查这个，但没有真正的解决方案！有人知道怎么解决吗?

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

推荐答案

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

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

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

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.

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

Here is how you should use CONCATENATE:

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

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

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

顺便说一下 - 您当前的字符串大约有 972 个字符.

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

你可以使用这个公式来简化分离:

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)

这篇关于Excel:使用超过 255 个字符的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！