带有if语句的255个字符的Excel/Formula限制 [英] Excel/Formula limitation with 255 characters with if statement

问题描述

早上好，我想用下面的excel公式检查单元格，看看它们是否属于A，B或C类别，

Good morning, I would like to check the cell to see if they are fall into A, B or C categories with the excel formula below,

=IF(CB3=0,"",
(IF(N3="
215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 324, 325, 326, 327, 328, 329, 330, 331, 336, ","A",

IF(N3="215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, "B",

IF(N3="301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 324, 325, 326, 327, 328, 329, 330, 331, 336, ","C",
IF(LEN(N3)=0,"",LEFT(N3,(LEN(N3)-2))))))))

我已经看到255个文本限制错误，因此我无法对其进行处理.excel表示我可以使用CONCATENATE或& ;，但不知道该如何处理.有什么建议么?提前谢谢！

And I've seen 255 text limits error so that I can't process it, The excel said that I can utilize a CONCATENATE or &, but have no idea what I can do with this. Any suggestions? Thanks much in advance!

推荐答案

正在抱怨要测试的一个非常长的文本字符串:

It's complaining about this one very long text string that you are testing for:

"215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 324, 325, 326, 327, 328, 329, 330, 331, 336, "

相反，使用串联将其分解:

Instead, use concatenation to break it up:

CONCATENATE("215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 246, 247, 248, 249, 250, 251,", " 252, 253, 254, 255, 256, 257, 301, 302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 324, 325, 326, 327, 328, 329, 330, 331, 336, ")

这两个在功能上是等效的，因为连接只需将两个字符串粘在一起成为一个长字符串.

These two are functionally equivalent as concatenate just sticks the two strings together into one long one.

所以...我们在这里要做的就是分解您很长的字符串，例如:

So... all we are doing here is breaking up your very long string like:

="This is a very long string, isn't it"

分成两个较小的字符串并将它们串联在一起:

Into two smaller strings and concatenating them together:

=Concatenate("This is a very long ", "string, isn't it")

这两个公式在功能上等效，因为结果值将为

These two formulas are functionally equivalent since the resulting value will be

This is a very long string, isn't it

这篇关于带有if语句的255个字符的Excel/Formula限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！