Blank cells: ISBLANK = FALSE, ISTEXT = TRUE
 

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

Blank cells: ISBLANK = FALSE, ISTEXT = TRUE
 

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" wrote in message
...
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

Blank cells: ISBLANK = FALSE, ISTEXT = TRUE
 

On Jan 4, 5:04*pm, "Bob Phillips" wrote:
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" wrote in message

...



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- Hide quoted text -

- Show quoted text -

Thanks, Bob. It worked.

Blank cells: ISBLANK = FALSE, ISTEXT = TRUE
 

On Jan 4, 4:04*am, "Bob Phillips" wrote:
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.