Inserting "true" blanks with functions
 

I'm having a problem inserting a blank in a cell using a function so that
another formula that refers to this blank reads it as a blank.

For example, if A1 is a blank cell:

Formula Result

=ISBLANK(A1) TRUE
=IF(ISBLANK(A1)," ",1)
=ISBLANK(A3) FALSE

In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank cell
in A3, a reference to it in another formula shows that Excel does not think
it is blank.

How do you write a formula so that there is truly nothing in the cell as a
result?

Inserting "true" blanks with functions
 

" " is a space and not a blank. Try the below formula....

=IF(TRIM(A1)="","Blank","Not Blank")

If this post helps click Yes
---------------
Jacob Skaria


"bearspa" wrote:

I'm having a problem inserting a blank in a cell using a function so that
another formula that refers to this blank reads it as a blank.

For example, if A1 is a blank cell:

Formula Result

=ISBLANK(A1) TRUE
=IF(ISBLANK(A1)," ",1)
=ISBLANK(A3) FALSE

In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank cell
in A3, a reference to it in another formula shows that Excel does not think
it is blank.

How do you write a formula so that there is truly nothing in the cell as a
result?

Inserting "true" blanks with functions
 

A formula cannot return a true blank cell. You have written a space with
your " " output, so the cell is certainly neither blank nor empty.
The best you can do is to change your =IF(ISBLANK(A1)," ",1) to
=IF(ISBLANK(A1),"",1) or to =IF(A1="","",1) and then you will be writing an
empty string.
Change your =ISBLANK(A1) test to =A1=""
--
David Biddulph

"bearspa" wrote in message
...
I'm having a problem inserting a blank in a cell using a function so that
another formula that refers to this blank reads it as a blank.

For example, if A1 is a blank cell:

Formula Result

=ISBLANK(A1) TRUE
=IF(ISBLANK(A1)," ",1)
=ISBLANK(A3) FALSE

In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank
cell
in A3, a reference to it in another formula shows that Excel does not
think
it is blank.

How do you write a formula so that there is truly nothing in the cell as a
result?

Inserting "true" blanks with functions
 

Don't use ISBLANK() to test a cell. ISBLANK() will return FALSE unless the
cell is truely empty. Even a nuill character is not empty. For example, in
A1 enter:

=""

copy A1 and paste/special/value into A2
leave A3 truely empty


ISBLANK(A1) will return FALSE
ISBLANK(A2) will return FALSE
ISBLANK(A3) will return TRUE
--
Gary''s Student - gsnu200860


"bearspa" wrote:

I'm having a problem inserting a blank in a cell using a function so that
another formula that refers to this blank reads it as a blank.

For example, if A1 is a blank cell:

Formula Result

=ISBLANK(A1) TRUE
=IF(ISBLANK(A1)," ",1)
=ISBLANK(A3) FALSE

In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank cell
in A3, a reference to it in another formula shows that Excel does not think
it is blank.

How do you write a formula so that there is truly nothing in the cell as a
result?

Inserting "true" blanks with functions
 

Why not just test the cell's value?

=IF(A1="","A1 is blank","A1 has something in it")

--
Rick (MVP - Excel)


"bearspa" wrote in message
...
I'm having a problem inserting a blank in a cell using a function so that
another formula that refers to this blank reads it as a blank.

For example, if A1 is a blank cell:

Formula Result

=ISBLANK(A1) TRUE
=IF(ISBLANK(A1)," ",1)
=ISBLANK(A3) FALSE

In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank
cell
in A3, a reference to it in another formula shows that Excel does not
think
it is blank.

How do you write a formula so that there is truly nothing in the cell as a
result?

Inserting "true" blanks with functions
 

Jacob, Gary, David and Rick:

I should have clarified that the formulae I'm showing in A2 and A3 are very
simple ones. In the spreadsheet that I'm working on, they are fairly
complex. All of you are right in that if they were as simple as the ones
shown, the approaches that you suggested would work.

I ended up using something along the lines of =IF(a3=" ",...,...), but
wanted to know if there was some way of inserting a blank, but it does not
seem feasible.

Thanks for all your suggestions.

"Rick Rothstein" wrote:

Why not just test the cell's value?

=IF(A1="","A1 is blank","A1 has something in it")

--
Rick (MVP - Excel)


"bearspa" wrote in message
...
I'm having a problem inserting a blank in a cell using a function so that
another formula that refers to this blank reads it as a blank.

For example, if A1 is a blank cell:

Formula Result

=ISBLANK(A1) TRUE
=IF(ISBLANK(A1)," ",1)
=ISBLANK(A3) FALSE

In the above example, even though =IF(ISBLANK(A1)," ",1) shows a blank
cell
in A3, a reference to it in another formula shows that Excel does not
think
it is blank.

How do you write a formula so that there is truly nothing in the cell as a
result?