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

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?

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

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.

Thank you!

There are comments and answers also discussing workarounds. (See my own answer of today e.g.)

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

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.

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).

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).

I wanted a way to make a cell, that contains a function to do calculations based on the content of another cell, look â€śemptyâ€ť when that other cell didnâ€™t contain any value.

This is the perfect answer for me. I wasnâ€™t aware of the {} construction to make a cell look â€śemptyâ€ť.

Great tip!

We can use {} in a function to get an empty cell as result. Brilliant! This is exactly was i was searching for.

Thank You. If i could i would up-vote Your answer, especially as the other above is basically wrong.

Excellent, {} works!

*This thread is very old, but having a subject that was clearly (though not fully consistent) worded, it is visited again and again. I originally contributed a soultion only by commenting on the answer posted as â€świkiâ€ť on 2015-04-29.
Meanwhile there was opportunity to learn that this anwer was unnecessarily complicated. The usage of OFFSET() or similar constructs can be omitted if not the calculated reference is needed. Thus I give a new answer with one old and unchangeable part â€ś-1-â€ť and the simple way to get something like a soultion as far as possible in part â€ś-2-â€ť.*

-1-

To get an actually blank cell by a resident formula is strictly impossible because the formula itself is content of the cell.

-2-

The simple way to get whatâ€™s next to blank if the referenced cell (A2 e.g.) is blank is the formula `{=A2:A2}`

.

(This is `=A2:A2`

entered for array-evaluation either by Ctrl+Shift+Enter or from the formula wizard.)

The answer is exemplified in **this** attachment.

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.

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

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

Hi

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 `Tools`

â–¸`Protect`

â–¸`Sheet`

â–¸uncheck `Allow all users to Select protected cells`

.

Regards

I do not have a solution, but an opinion: THIS SHOULD BE SOLVED ADDING EXTRA VALUE, for an example EMPTY() which means that will be considered like 0 (zero) in every calculationts and will always look like empty. Solutions like =IF(B4=0;{};B4) or =IF(B4=0;"";B4) does not solve the problem, if the function is in cell A2, it must be calculated on cell A4: =A2+A3 now it returns #VALUE!. What is a bit amazing is that functtion =SUM(A2:A3) can calculate the value 0, even if A2 is {} or â€śâ€ť, but =A2+A2 cannot do this.