Formula in B1
 

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.

Formula in B1
 

"Trainee" wrote:
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 -----

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

Formula in B1
 

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",""))

--
Kind regards

Rik


"Trainee" wrote:

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.

Formula in B1
 

"Rik_UK" wrote:

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",""))

--
Kind regards

Rik


"Trainee" wrote:

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.

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.

Formula in B1
 

Sure use the following

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


--
Kind regards

Rik


"Trainee" wrote:



"Rik_UK" wrote:

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",""))

--
Kind regards

Rik


"Trainee" wrote:

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.

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.

Formula in B1
 

Try this:

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

Hope this helps.

Pete (also from the UK)

On Dec 16, 12:51*am, Trainee
wrote:
"Rik_UK" wrote:
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",""))

--
Kind regards

Rik

"Trainee" wrote:

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.

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

- Show quoted text -

Formula in B1
 

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

Pete

On Dec 16, 1:01*am, Rik_UK wrote:
Sure use the following

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

--
Kind regards

Rik



"Trainee" wrote:

"Rik_UK" wrote:

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",""))

--
Kind regards

Rik

"Trainee" wrote:

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.

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

- Show quoted text -

Formula in B1
 

"Rik_UK" wrote:

Sure use the following

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


--
Kind regards

Rik


"Trainee" wrote:



"Rik_UK" wrote:

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",""))

--
Kind regards

Rik


"Trainee" wrote:

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.

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.

Works very well, Thanks again.

Formula in B1
 

"Pete_UK" wrote:

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

Pete

On Dec 16, 1:01 am, Rik_UK wrote:
Sure use the following

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

--
Kind regards

Rik



"Trainee" wrote:

"Rik_UK" wrote:

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",""))

--
Kind regards

Rik

"Trainee" wrote:

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.

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

- Show quoted text -

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