Some cells in my spreadsheet are truly blank, some look blank but are not blank:
LEN()=1
ISBLANK()=FALSE
See attached:
Empty cells problem.ods (18.8 KB)
This is a problem because formulas return the value of the blank-but-not-blank cell as 0.
This must be normal behaviour: can anyone tell me what’s going on, and how to deal with it, in general terms?
Either here, or maybe documentation. Many thanks.
PS I can workaround with TRIM, but I’d still like to understand what’s going on! 
TRIM is not a “workaround”, it’s “what’s going on” - its job is to remove whitespace characters from start and end of a string (and deduplicate whitespace in the middle). So you could figure, that your cells must have whitespace characters. Isn’t that pretty obvious? The space is not visible, yet, it’s there, and the cell is not blank because of it.
Yes, it’s pretty obvious. So I want to know:
1 How do such spaces get there?
The data is from GOV.UK: some empty cells are blank and some have whitespace.
It also seems that I myself have created some cells that are truly blank, and some that have whitespace (though I’m less sure about that).
2 Why it is true that ISBLANK(A1)=TRUE, and it is also true that =A1=0
3 Whether there are general principles I can learn for how people normally deal with this.
I found your answer to a similar question here, and the behaviour seems (to me) to be a riot of inconsistency. Fine, every programme has its funny ways; I just need to understand them so I can work with them.
I bet that people at GOV.UK can explain that to you on inquiry. Don’t ever expect data coming from people to be perfect. When you press a spacebar, you put a space to the current cell.
Because the standard says so.
That’s how I understood it to work, but there seems to be more, where TRIM works on an empty cell: I can return empty, rather than 0 - I don’t know why.
A1 is empty
=LEN(A1)=0
=ISBLANK(A1)=TRUE
=A1=0
=TRIM(A1)=(empty)
What you ask is unclear. TRIM removes spaces when they are there. Its result is a string, not a number.
Here there are no spaces to remove, and TRIM does not return 0
That helps me practically. It would be nice to know why it does that.
As I wrote:
Ok, so the default is that Calc deals in numbers, unless some formula intervenes to give a string?
(That would be unsurprising, given the name of the program…)
@mikekaganski:
This looks as if you are convinced that everything is OK with the type handling and related questions in Calc. I’m not.
Unfortunately I can’t show everything I judge to be relevant in the context without a 4-liner UDF.
See:
disask112448related_CellResultTypesConfusion.ods (18.4 KB)
The “results” shown in D10, D31, D36 seem to indicate regression bug(s). The oldest version from my “museum” showing the behaviour is V4.4.7 (32 bit from PortableApps package). V4.2.8 did not.
The UDF problem is due to the fact that FormulaResultType2 is only availabe in V6.1 or higher.
to recognize the input of a cell have a look to some possibilities of non visible contents below row [9] to help to understand the formula interrogations:
empty cells problem_UNiCODE()_112134.ods (15.5 KB)
This is very helpful: thank you.