Find a blank non-empty cell

[LeroyG]

Hello all,

Given a formula that gives nothing (i.e. ="" or =IF(A1=A2;"";"x") for A1=A2), choosing menu Data - Calculate - Formula to Value returns a blank non-empty cell (content.xml says <text:p/>).

I can’t find a way to Find and Replace them in situ.
Tried with: ^$, [:space:] and [:cntrl:], and Alt+X gives nothing.

Is there another option to Find it?

Thanks.

Just filled a bug report tdf#144986.

As a workaround, I sorted these rows and erased the non-empty cells. It was not much difficult, because the problematic content was in one column.

[erAck]

Strictly spoken the behaviour is correct, but the result of an empty text content should be searchable (it might not be because such can’t be entered via UI). If an empty string was replaced with a blank cell, semantics would change like

• A1: =""
• A2: =A1+2
• => #VALUE! (if strict text conversion is enabled)

With A1 being empty the result would change to 2.

Or a simple =A1 would change from empty display string to 0, or ISBLANK(A1) change from FALSE to TRUE.

[Lupp]

Is Copy(Ctrl+C) followed by Paste Special... (Ctrl+Shift+V) with Formulas disabled an option for you?
It should do what you want.

Testing with V 7.2.1.2: The bug you found will also copy/paste the inconsistent state (no content at all, but still type TEXT) if already present in advance. It will convert a cell with a formula returning the empty string to a blank cell, however, as you expect…
An inspection of an afflicted cell actually shows empty formula and empty string. This should qualify the cell as blank. In fact it hasn’t .Type=0 (blank) but .Type=2 (text).

Concerning the terms: I used “blank” In the sense the The Calc function ISBLANK() is suggesting.

[sokol92]

The difference between an empty cell (for example, A1) and a cell containing text of length 0 (for example, A2) is not only evident in the case of ISBLANK function.
For example, formulas =A1=A2 and =A1=0 return True, but formula =A2=0 returns False (same in Excel).

[LeroyG]

@Lupp

Paste Special... with Formulas` disabled

For a moment I thought I had missed something obvious, but that don’t work. Thanks anyway.
I also tried Ctrl+Shift+Alt+V to no avail.

The (no-)bug report has a bit more of information.

[eeigor]

Handling of Empty Cells