Blank Cell

[jbodel]

I have had a formula in my worksheet for a while that says the following, =if(ISBLANK(AC29),0,2). For some reason this has stopped working. After re-typing the formula it works once, but once you change the value back to blank in AC29, it does not change to 0. I have also tried =if(AC29="",0,2) and that does not work.

Am I doing something silly that I am just not thinking of? Do I have a setting that is causing this to not work anymore?

 

[Joe4]

Does this work?

=if(LEN(TRIM(AC29))=0,0,2)

 

[jbodel]

Does this work?

=if(LEN(TRIM(AC29))=0,0,2)

Unfortunately it does not. It changes it originally to a 2, but if you delete the content in cell AC29, it stays at a 2.

It's driving me crazy, I don't know what happened.

 

[Joe4]

OK, what does this return?

=LEN(AC29)

If it returns anything other than 0, it means you actually have SOMETHING in that cell, even though you cannot see it.
It could be an invisible character like a non-breaking space.

Where exactly are these values in AC29 coming from?
Are they downloaded from the web, or extracted from another program?
I often see special invisible characters found in those kinds of data extracts.

 

[RobP]

or you have somehow switched your worksheet calculations to "manual" rather than auto ... ?

Just a thought. Have a look under "Formulas" / Calculation Options

Rob

 

[jbodel]

OK, what does this return?

=LEN(AC29)

If it returns anything other than 0, it means you actually have SOMETHING in that cell, even though you cannot see it.
It could be an invisible character like a non-breaking space.

Where exactly are these values in AC29 coming from?
Are they downloaded from the web, or extracted from another program?
I often see special invisible characters found in those kinds of data extracts.

The Contents in AC29 are manually entered. So when I first tried your formula and I typed something into AC29 it worked. But when I went back to AC29 and just clicked the delete button and clicked enter it did not change back to 0. I am certain there are no spaces or anything in that cell. Now it is a merged cell AC29-AE29, but I just tried unmerging them and it still does not work.

 

[jbodel]

or you have somehow switched your worksheet calculations to "manual" rather than auto ... ?

Just a thought. Have a look under "Formulas" / Calculation Options

Rob

That was it. I wonder how that got changed. I honestly don't remember changing anything like that, nor (honestly) did I know that even existed.

Thank you both for all your help

 

[RobP]

You're welcome, thanks for the feedback.

I believe Sometimes it can be changed from an unsuspecting other file that you have opened .. and remain set until you change it back again.

Rgds
Rob