问题描述
我在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函数或连接运算符 (&).
我一直在网上查这个,但没有真正的解决方案!有人知道怎么解决吗?
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 个字符的公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!