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?

--
Gary''s Student - gsnu2007xx

On Thu, 7 Feb 2008 06:51:04 -0800, Gary''s Student
wrote:

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?


=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

On Thu, 7 Feb 2008 06:51:04 -0800, Gary''s Student
wrote:

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?

Something like:

=IF(ISBLANK(A1),1,IF(AND(LEN(A1)=0,CELL("prefix",A 1)="'"),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

Excellent!!!

This is exactly what I need!
--
Gary''s Student - gsnu200767


"Ron Rosenfeld" wrote:

On Thu, 7 Feb 2008 06:51:04 -0800, Gary''s Student
wrote:

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?

Something like:

=IF(ISBLANK(A1),1,IF(AND(LEN(A1)=0,CELL("prefix",A 1)="'"),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

On Thu, 7 Feb 2008 07:24:00 -0800, Gary''s Student
wrote:

Excellent!!!

This is exactly what I need!
--
Gary''s Student - gsnu200767

Glad to help. Thanks for the feedback.
--ron

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary''s Student" wrote in message
...
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?

--
Gary''s Student - gsnu2007xx

thanks
--
Gary''s Student - gsnu200768


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary''s Student" wrote in message
...
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?

--
Gary''s Student - gsnu2007xx