Inserting "true" blanks with functions

B

bearspa

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?
 
J

Jacob Skaria

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

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

If this post helps click Yes
 
D

David Biddulph

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=""
 
G

Gary''s Student

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
 
R

Rick Rothstein

Why not just test the cell's value?

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

bearspa

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top