Excel Formula Help

J

JC

I need a formula that returns a "1" in Cell B4 if A4 has text and a "0" if A4
is a blank cell.
 
D

Don Guillett

This will return 1 if text or nothing if not blank and not text, or 0 for
blank

=IF(LEN(TRIM(A7))>0,IF(ISTEXT(A7),1,""),0)
 
J

joeu2004

I need a formula that returns a "1" in Cell B4 if A4 has
text and a "0" if A4 is a blank cell.
Click to expand...

Unfortunately, the phrases "has text" and "is blank" are ambigous.
Excel makes a distinction between text and numeric values. And Excel
makes a distinction between an empty cell (i.e. no formula or
constant) and a cell whose value is the null string; both appear
"blank", as well as a string of spaces.

If we take your request literally, then what do you want if neither
condition is true?

Perhaps the following will meet your needs (but I doubt it).

=if(istext(A4), 1, if(A4="", 0, ""))

Note that A4="" is true is A4 is empty, or A4 contains a value that is
the null string like the last else-expression of the second IF
function above.
 
D

David Biddulph

=IF(ISTEXT(A4,1,IF(ISBLANK(A4),0,"A4 may have a number?"))

If, of course you don't want to distinguish between text and a number, the
formula could be changed, and similarly if you don't want to draw a
distinction between a blank cell and an empty text string.
Another possibility, depending on the answer to those questions, might be
=IF(A4="",0,1) or even =--(A4<>"")

I have assumed that you want B4 to contain a number 0 or 1, rather than text
strings "0" or "1".
 
Top