Ask Your Question

How to return empty cell from function in calc

asked 2014-05-14 11:07:11 +0100

denis.polkovnikov gravatar image

updated 2014-05-14 11:14:32 +0100

I want get a completely empty cell at return without using quotes, i.e. not like at previous question

edit retag flag offensive close merge delete


Ctrl + arrow is fetches an empty string as two double quotes. What I do not want as return of function. Is there a way to do so?

denis.polkovnikov gravatar imagedenis.polkovnikov ( 2014-05-14 11:20:19 +0100 )edit

Yet another case is pivot table. It adds (empty) result.

denis.polkovnikov gravatar imagedenis.polkovnikov ( 2014-05-28 06:47:41 +0100 )edit

4 Answers

Sort by » oldest newest most voted

answered 2014-05-14 13:10:27 +0100

Regina gravatar image

It is in principle impossible. The return value of a formula is shown in the very same cell, which contains the formula. And because this cell contains the formula it is never "empty". Otherwise a formula would erase itself.

If the empty string is a valid result and you need to indicate, that the formula result is not valid, then you can use the function NA(). It generates the special error value #N/A, which can be tested by the function ISNA().

If you need to erase the content of a cell dependent on the values of other cells, you need a macro to do that.

edit flag offensive delete link more


Thank you!

denis.polkovnikov gravatar imagedenis.polkovnikov ( 2014-05-19 12:09:55 +0100 )edit

answered 2014-05-14 13:25:26 +0100

Pedro gravatar image

The short answer seems to be no.

Following on Regina's logic answer (a cell containing a formula is not empty because it contains the formula): because a cell returning "" is interpreted as containing Text you can use an IF(ISTEXT(A1);0;A1) condition to solve (some) problems.

edit flag offensive delete link more


I asked about returning empty cell because I want to use Ctrl + arrow. Not for using formula. Formula do well with empty cells.

denis.polkovnikov gravatar imagedenis.polkovnikov ( 2014-05-19 12:15:09 +0100 )edit

answered 2015-04-29 22:38:49 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

In order to provide an answer, here's my two cents that actually do exactly what is asked.

If you're doing a conditional on, let's say, cell B4:

then you can do this: =IF(B4=0;{};B4)

This results in a blank cell if the value of B4 is zero and returns B4 when it is not.

Best, L

edit flag offensive delete link more


The cell containing the formula will answer FALSE if asked =ISBLANK(TheCell). It will also return FALSE to =ISTEXT(TheCell) and to =ISNUMBER(). We may consider this "virtually blank". The fact, however, that the cell is containing a formula is unchangeable - and to =ISFORMULA(TheCell) we get a TRUE.

In my test with V4.2.2 the "pseudoblank" result of the suggested formula persisted when the value of B4 was changed to something <>0. I regard this as a bug.

Lupp gravatar imageLupp ( 2015-04-29 23:39:35 +0100 )edit

Coming next to reproducing B4 even if BLANK may be the array formula {=OFFSET(B4,0,0,1,1)} in TheCell. It will, however, also answer TRUE to =ISFORMULA(TheCell) and FALSE to =ISBLANK(TheCell).

Lupp gravatar imageLupp ( 2015-04-29 23:45:22 +0100 )edit

Still Ctrl + arrow is fetches cell with ={} although it looks empty. Interesting enough pivot table recognizes ={} as zero and ="" as second (empty) whereas totally empty as the first (empty).

denis.polkovnikov gravatar imagedenis.polkovnikov ( 2015-12-23 15:26:41 +0100 )edit

answered 2016-05-10 12:01:42 +0100

pierre-yves samyn gravatar image


My answer may be too late for this, sorry, but if the goal is:

Ctrl+Arrow should not find a cell displaying "" (result of a formula)

a solution could be to protect these cells (eg . apply a cell style) then ToolsProtectSheet▸uncheck Allow all users to Select protected cells.


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2014-05-14 11:07:11 +0100

Seen: 8,688 times

Last updated: May 10 '16