IF AND formula

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

HI

I've been trying to say this :

IF H2 does not contain 'Blue' and B2 is empty , then put "123456".
IF H2 does not contain 'Blue' and B2 has content , then put B2

Can someone help with a formula to satisfy this?

Thanks.
 
=IF(ISERR(FIND("Blue",H2)),IF(LEN(B2)=0,123456,B2))

Notes:
1. With regard to Blue, this formula is case-sensitive. If you
want it to be case-insensitive, change the FIND to SEARCH.

2. You have not defined what you want to happen if B2 contains
Blue. This formula will return FALSE if that is the case.
Click to expand...

Hi Ron

OK Thanks for your time and expertise. This works perfectly and fits
exactly what I was trying to do.

In answer to your second question , I don't need anything to happen if
B" contains Blue. So I was able to remove the FALSE return with a search
and replace once I have pasted from my helper column.

BTW I was just going to post another puzzle I have.

This is my failing formula :

=IF(LEN(B2)=12,3,""),IF(LEN(B2)=13,4,"")

I'm trying to say this ;

If the number in B2 has exactly 12 characters , put 3.
If the number in B2 has exactly 13 characters , put 4.
Otherwise leave it blank.


If you can help on this too, I'd be very grateful. Hope I'm not being
cheeky.


Best wishes.
 
HI

I've been trying to say this :

IF H2 does not contain 'Blue' and B2 is empty , then put "123456".
IF H2 does not contain 'Blue' and B2 has content , then put B2

Can someone help with a formula to satisfy this?
Click to expand...

Not until you supply the missing piece: what if H2 *does* contain
"Blue"?

At this point you have

=if(H2="Blue", ???????????????, if(isblank(B2),"123456",B2) )
 
In answer to your second question , I don't need anything to happen if
B" contains Blue.
Click to expand...

Something *has* to happen. A formula always returns a value, even if
it's an error value.
 
Ron Rosenfeld said:
Would a null string do?

If so, change the formula:

=IF(ISERR(FIND("Blue",H2)),IF(LEN(B2)=0,123456,B2),"")


=IF(LEN(B2)=12,3,IF(LEN(B2)=13,4,""))
Click to expand...

Hi

OK thanks for your help and considerable expertise.

Both formulae work perfectly. Thanks again - I'm grateful.



Best Wishes
 
Back
Top