Blank cells: ISBLANK = FALSE, ISTEXT = TRUE

[Raj]

Hi,

Depending on the values in column U, I want column V to have a value.
I am using an IF formula for this.
There is one problem. Some cells in Column U (eg U26) are blank(have
no values). However if I user U26 ="" for testing the condition, this
is resulting in FALSE. If I use =ISTEXT(U26), this is resulting in
TRUE. Similarly, ISBLANK(U26) is returning FALSE. There is no formula
in the cell. The column (U) is formatted as General. However, when I
press "Delete" in U26, ISBLANK returns TRUE and ISTEXT returns
FALSE. What could be wrong and how can I handle this?
One solution is to "Delete" the contents of the Blank cells. This is a
bit cumbersome as such blank cells are scatterred across the column. I
am posting this as the knowing the cause of the problem and the
solution would help me on future occasions also.

Thanks in advance for all the help.

Regards,
Raj

 

[Bob Phillips]

Sounds like you have a space(s) in there.

Maybe, try

=LEN(TRIM(J27))=0

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Raj]

Sounds like you have a space(s) in there.

Maybe, try

=LEN(TRIM(J27))=0

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)









- Show quoted text -

Thanks, Bob. It worked.

 

[joeu2004]

Sounds like you have a space(s) in there. Maybe, try
=LEN(TRIM(J27))=0

Why not simply:

=(TRIM(J27)="")

I try to avoid function nesting, not only for efficiency, but also to
avoid hitting the limit of 7 before Excel 2007.