Well, how empty is it?

  • Thread starter Gary''s Student
  • Start date
G

Gary''s Student

Is it possible, without VBA, to test the “emptiness†of a cell? For example,
a formula that will return 1 if a cell is unused (has nothing in it), 2 if
the cell has a formula returning a null string:

=IF(1=1,â€â€,â€â€)

3 if the cell has a single quote in it and 4 otherwise?
 
R

Ron Rosenfeld

Is it possible, without VBA, to test the “emptiness” of a cell? For example,
a formula that will return 1 if a cell is unused (has nothing in it), 2 if
the cell has a formula returning a null string:

=IF(1=1,””,””)

3 if the cell has a single quote in it and 4 otherwise?
Click to expand...


=IF(ISBLANK(A1),1,IF(LEN(A1)=0,2,IF(NOT(ISERR(FIND("'",A1))),3,4)))

Of course, this is testing for the presence of a single quote -- NOT for the
use of a single quote to tell Excel to format the rest of the entry as TEXT.
--ron
 
R

Ron Rosenfeld

Is it possible, without VBA, to test the “emptiness” of a cell? For example,
a formula that will return 1 if a cell is unused (has nothing in it), 2 if
the cell has a formula returning a null string:

=IF(1=1,””,””)

3 if the cell has a single quote in it and 4 otherwise?
Click to expand...

Something like:

=IF(ISBLANK(A1),1,IF(AND(LEN(A1)=0,CELL("prefix",A1)="'"),3,IF(LEN(A1)=0,2,4)))

detects if the single quote is present by itself and as a prefix character.

But you really didn't specify that.
--ron
 
D

Don Guillett

try this logic to get to the last one
=trim(a1)
=len(trim(a1))
=if(len(trim(a1))<1,1,2)
 
Top