Formula problem

N

nic17

Hiya,

I need some help.

I am using thr formula below:

=IF("BK"=" ",1,0)

The column it is reading from, for some reason, in all the blank cell
there are 5 spaces. This is just the way it is pulled out of SAP, s
cannot alter this.

This formula sucessfully works in other spreadsheets, so why can't
get it to work in this one?

Most of the 33,000 rows are blank, but this formula just keep
reporting '1' for every row!

I've checked, and checked again, and there is definantly only 5 space
too
 
A

Andy B

Hi

I'm surprised that you are getting a 1 at all! Your formula seems to be
asking whether BK is equal to " " - which it isn't! Post some of your
data (plain text) for us to look at.
 
N

nic17

My data looks like this below. Three columns. My main aim is to
produce a column that says: If either of the three colums is populated
then 'true'. If all blank, then 'false'.

But, don't forget that all blank cells have five spaces, if that makes
a difference.

Blk H Blk I Blk D
1
18


18

7
ZS

8



ZS



8
8
8
 
J

Jerry W. Lewis

I don't see what your original formula has to do with your description,
but something like
=IF(TRIM(H1&I1&J1)="",FALSE,TRUE)
would seem to match your description

Jerry
 
N

nic17

Thanks,

This seems like a better approach than what i had planned to do, but
cannot get it to work.

I think the five spaces in the blank cells are causing the problem.

Any ideas
 
J

Jerry W. Lewis

More information, please. What happens when you use this?

Trim would drop the spaces, assuming that they really are spaces. Try
=CODE(H1)
If the first character is a space, it should return 32. If you get 160,
then they just look like spaces, but will not be removed by the TRIM
function. In that case, you would need to expand the formula to
=IF(TRIM(SUBSTITUTE(H1&I1&J1,CHAR(160)," "))="",FALSE,TRUE)

Jerry
 
Top