• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What happened to the rest of it...?!

Eloise T

Eloise T

Active Member
The following formula lives and runs in one of my "filter" spreadsheets in cell O7:

'=IF(N7<>0,IF(H7=5,10,IF(AND(H7=10,IFERROR(SEARCH("*Cancel*",J7)>0,0),IFERROR(SEARCH("*24*",J7)>0,0)),20,IF(AND(H7=10,IFERROR(SEARCH("*Cancel*",J7)>0,0),IFERROR(SEARCH("*OSS*",J7)>0,0)),30,IF(AND(H7=15,OR(ISNUMBER(SEARCH({"*EU*","*SNR*","*Site*"},J7)))),30,IF(OR(ISNUMBER(SEARCH({"*Diag*","*ware*","*FWU*"},J7))),40,IF(AND(OR(H7=45,H7=65),N7<51,IFERROR(SEARCH("*OSS*",J7)>0,0)),100,IF(AND(OR(H7=45,H7=65),N7>50,N7<66,IFERROR(SEARCH("*OSS*",J7)>0,0)),130,IF(AND(OR(H7=45,H7=65),N7>65,IFERROR(SEARCH("*OSS*",J7)>0,0)),140,IF(AND(OR(H7=45,H7=65),N7<51),80,IF(AND(OR(H7=45,H7=65),N7>50,N7<80),110,IF(AND(OR(H7=45,H7=65),N7>79),135,IF(AND(OR(H7=30,H7=50),N7<51,IFERROR(SEARCH("*OSS*",J7)>0,0)),70,IF(AND(OR(H7=30,H7=50),N7>50,N7<66,IFERROR(SEARCH("*OSS*",J7)>0,0)),100,IF(AND(OR(H7=30,H7=50),N7>65,IFERROR(SEARCH("*OSS*",J7)>0,0)),110,IF(AND(H7>69,N7>65,IFERROR(SEARCH("*OSS*",J7)>0,0)),135,IF(AND(OR(H7=30,H7=50),N7<51),50,IF(AND(OR(H7=30,H7=50),N7>50,N7<80),80,IF(AND(OR(H7=30,H7=50),N7>79),105,IF(AND(H7>69,N7>69,N7<80),105,IF(AND(H7>69,N7>79),130,"√")))))))))))))))))))),"--")

The formula works just fine; however, when I copy it and paste it as a comment in another part of my spreadsheet, cell A1 in the attachment, it gets truncated and stops at the red character F above.

Any explanations come to mind?

See attached spreadsheet. Thank you.
 

Attachments

  • Chandoo - comment truncation conundrum.xlsx
    10.1 KB · Views: 5
Don't know about the max number of characters in comments.
OTH your formula should be easier to read if you remove the >0 in parts like IFERROR(SEARCH("*Cancel*",J7)>0,0)
The SEARCH function does not return negative values AFAIK
 
Hi ,

I tried the same , and I don't face any problem ; the entire formula is pasted into a comment.

Which version of Excel are you using ?

Narayan
 
Hi ,

I don't find any problem.

See the attached file.

Narayan
 

Attachments

  • Chandoo - comment truncation conundrum.xlsx
    11.7 KB · Views: 5
NARAYANK991 said:
Hi ,

I don't find any problem.

See the attached file.

Narayan
Click to expand...
When you
NARAYANK991 said:
Hi ,

I don't find any problem.

See the attached file.

Narayan
Click to expand...
First of all, I misspoke. I said comment when I meant to say using the apostrophe to force Excel to treat the formula line as text.

If you look at cell FQ1, the line stretching from cell A1 is truncated.

Is there a limit to the number of characters in a cell? I have a "Formula tab" in my worksheets where I keep the formulas for reference. This lengthy formula doesn't want to completely display.

Any further ideas?
 
SirJB7 said:
Hi, Eloise T!
Checked this yet?
https://support.office.com/en-us/ar...-467e-8e27-269d656771c3#ID0EBABAAA=Excel_2007
32K characters is the cell limit, which you don't have as length of A1 is 1074.
Maybe any other constraints are applying, like nested IFs.
Hope it helps.
Regards!
Click to expand...
If you look at cell A1, and view FQ1, you'll see A1 has been truncated. If you remove the apostrophe, it all returns.

True, since I'm not even close to 32K cell character limit, I've not exceeded the cell character capacity.... and if you remove the apostrophe, all is good, as also duplicated in cell O7.

Since the apostrophe makes it text, the IF functions, or anything else going on in the line, should not matter, correct?

I guesstimated I was at around 1000 characters. What did you use to make the actual count? I found MS Word [Review] > Proofing > [Word Count]
 
Last edited:
Hi ,

I think this has nothing to do with cell character limit. Removing the apostrophe is not really germane to the issue , since the main point to be understood is that the cell contents are not being truncated. It is only the display which is showing a truncated string.

Even if you do not remove the apostrophe , just turning on Word Wrap displays the entire formula text.

What happens is that the display truncates at 1024 , and therefore I assume this is the limit as far as display in non-Word Wrap mode is concerned.

I think this may be the limit of the column width , which is why Excel may be truncating the display at this stage.

As far as detecting the length of a text string is concerned , the LEN function does this.

Narayan
 
Last edited:
You must log in or register to reply here.
Back
Top