Formula in B1

[Trainee]

Could somebody be so kind and provide me with a formula to go in
B1 that will change this cell accordinly to A1.
If A1= blank, then B1= blank
If A1= "green" or "blue" or "plant" or "street" (TEXT),then B1 will show
letter "f".(all examples without quotation of course)
If A1= any other different word, then B1= blank.
Thank you very much.

 

[Joe User]

If A1= blank, then B1= blank
If A1= "green" or "blue" or "plant" or "street" (TEXT),then B1 will show
letter "f".(all examples without quotation of course)
If A1= any other different word, then B1= blank.

You have over-specified the requirements. In B1, the following is
sufficient:

=if(OR(A1={"green","blue","plant","street"}), "f", "")

The quotes are required around string constants. They will not appear, and
they will not be considered part of the text.

PS: Note that you cannot leave B1 "blank" (empty), if by that you mean that
ISBLANK(B1) returns TRUE. But B1="" will be true, whether B1 is empty (no
formula and no constant value) or the null string (""). ISBLANK is a
misnomer :-(.


----- original message -----

 

[Rik_UK]

As a blank in a1 or any othoer word than the ones listed have the same result
they need not be defined... putting this formula in cell b1 will provide the
results requested.

=IF(OR(A1="green",A1="blue",A1="plant",A1="street"),"f","")

but if when A1 is blank needs to be considered seperately then use the
following

=IF(A1="","",IF(OR(A1="green",A1="blue",A1="plant",A1="street"),"f",""))

 

[Trainee]

As a blank in a1 or any othoer word than the ones listed have the same result
they need not be defined... putting this formula in cell b1 will provide the
results requested.

=IF(OR(A1="green",A1="blue",A1="plant",A1="street"),"f","")

but if when A1 is blank needs to be considered seperately then use the
following

=IF(A1="","",IF(OR(A1="green",A1="blue",A1="plant",A1="street"),"f",""))

It works good, Thanks Rik Uk.
Now can the same formula be use with range of words instead of
writing each one each time. This come for another worsheet that
has 50 words and I need the "f" in B1 too.
The range is called "listedwords"
Thanks again.

 

[Rik_UK]

Sure use the following

=IF(ISERROR(MATCH(A1,listedwords,0)),"","f")

 

[Pete_UK]

Try this:

=IF(A1="","",IF(ISNA(MATCH(A1,listedwords,0)),"","f"))

Hope this helps.

Pete (also from the UK)

 

[Pete_UK]

Sorry, Rik, your post wasn't visible when I responded - I thought you
might have called it a day.

Pete

 

[Trainee]

Sure use the following

=IF(ISERROR(MATCH(A1,listedwords,0)),"","f")

Works very well, Thanks again.

 

[Trainee]

Sorry, Rik, your post wasn't visible when I responded - I thought you
might have called it a day.

Pete



.
Thank you too Peter Uk, you guys are doing a good job.