blocks|key|4535128|text|解决这一问题的最容易的方法是使用一些单元格来构建字符串的单独元素，并使用concetanate将它们合并到一个单元格中。它为解决错误提供了更多的空间--也许您的语句有错误，但它们的长度几乎是不可见的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4535129|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|4535178|text|这是一个混乱的功能。您必须分离对CONCATENATE的输入。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4535179|现在只有一个非常大的字符串开始于“插入.”结束于"....MID(O2,3,2)，“--或者至少，我认为这是结束的地方，因为所有的"，”它真的很难通过。|4535180|下面是如何使用CONCATENATE|4535181|=CONCATENATE("insert+into+#UpdateData+(mondayopenhour,","mondayopenmin,")|code-block|syntax|javascript|4535182|您可以扩展这一点，这样就不会有一个长度超过255个字符的字符串。|4535183|编辑:顺便说一句-您当前的字符串大约有972个字符。|4535184|您可以通过使用以下公式来简化分离：|4535185|=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)|4535186|entityMap^0|G|B|0|0|7|B|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|J|3|K|5|L|7|15|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|16|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|17|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|18|8|@]|D|@]|E|$]]|$1|U|3|V|5|L|7|19|8|@]|D|@]|E|$M|N]]|$1|W|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|X|$]]

Now this is one messy function. You have to seperate your input to <code>CONCATENATE</code>.

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.

Here is how you should use <code>CONCATENATE</code>:

<pre><code>=CONCATENATE("insert into #UpdateData (mondayopenhour,","mondayopenmin,")
</code></pre>

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

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

you can ease the seperation by using this formula:

<pre><code>=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)
</code></pre>

blocks|key|3655564|text|我也碰到了这个问题..。但这并不是什么问题。您只能在一个单元格中拥有255个字符，但在一个公式中可以没有限制。您的公式将导致错误，因此excel正在将其读取为文本而不是公式。找出你的错误，你的公式就会起作用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3655565|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|4535272|text|至于我对同样问题的解决方案，这些长文我把它放在一个单独的单元格上，而不是我公式本身的一部分。示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4535273|=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,");")|code-block|syntax|javascript|4535274|相反，我将其存储到一个单元格A2中。|4535275|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+('|4535276|所以我现在的公式是|4535277|=CONCATENATE(+A2,+B2,+C2)|4535278|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

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:

<pre><code>=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,");")
</code></pre>

Instead, I store this to one of the cell A2

<pre><code>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 ('
</code></pre>

So my formula now is

<pre><code>=CONCATENATE( A2, B2, C2)
</code></pre>

blocks|key|4535312|text|我用“超过255个字符”来解决这个问题，而且不容易连在一起：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4535313|我将长串的值放入单元格T1中。我的公式如下：|offset|length|style|CODE|4535314|="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&"');"|code-block|syntax|javascript|4535315|entityMap^0|0|B|2|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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

I put into cell <code>T1</code> the long string of Values.
Then my Formula was as below:

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

blocks|key|4535317|text|我在使用公式时遇到了255个字符的问题，在字符串之间使用了"&“操作符，问题得到了解决。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4535318|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I came across the problem of 255 characters while using a formula
I used the &quot;&amp;&quot; operator in between the String and the problem is solved.

I have the following formula in excel

<pre><code>=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),")")
</code></pre>

When I try run it i get the following error 

<blockquote>
 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 (&amp;).
</blockquote>

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

Excel: Use formula longer that 255 characters

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在excel中有以下公式=CONCATENATE("insert into #UpdateData (mondayopenhour, mondayopenmin,mondayclosehour, mondayclosemin,tuesdayopenhour, tuesdayopenmin,tuesdaycloseho...

问Excel:使用公式长度为255个字符
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel:使用公式长度为255个字符EN