CODEWITHSUNDEEP
excelexcel-formulavba
zzaewon
zzaewon

Reputation: 29

Excel/Formula limitation with 255 characters with if statement

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))))))))

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!

Upvotes: 0

Views: 4320

Answers (1)

JNevill
JNevill

Reputation: 50273

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

Upvotes: 2

Related Questions