I have cells that have phrases written in them. Now I wish to assign a value
to the corresponding cell in the next column if a particular word is present
in the phrase.

e.g. Column A Column B
Johnny Passed the exam If A1 contains Johnny,
write 1 else 2
Rita missed the train If A1 contains Johnny,
write 1 else 2

How do I achieve the formula for column B?
Thanks in advance.

Try the following:-

=IF(ISERROR(FIND("Johnny",A1,1)),"2", "1")

FIND is case sensitive. Use SEARCH in lieu as follows for non case sensitive:-

=IF(ISERROR(SEARCH("johnny",A3,1)),"2", "1")

--
Regards,

OssieMac


"Malik" wrote:

I have cells that have phrases written in them. Now I wish to assign a value
to the corresponding cell in the next column if a particular word is present
in the phrase.

e.g. Column A Column B
Johnny Passed the exam If A1 contains Johnny,
write 1 else 2
Rita missed the train If A1 contains Johnny,
write 1 else 2

How do I achieve the formula for column B?
Thanks in advance.

thanks.
wt shud i do if I have more than one words to search for? I know I can use
IF as many times as the words but the words that I have to search for are
around 35.

I want to search that "is any of these 35 words present in the phrase in A2?
If yes, condition1, if no condition2".

Your help is much appreciated.

Thanks again.

"OssieMac" wrote:

Try the following:-

=IF(ISERROR(FIND("Johnny",A1,1)),"2", "1")

FIND is case sensitive. Use SEARCH in lieu as follows for non case sensitive:-

=IF(ISERROR(SEARCH("johnny",A3,1)),"2", "1")

--
Regards,

OssieMac


"Malik" wrote:

I have cells that have phrases written in them. Now I wish to assign a value
to the corresponding cell in the next column if a particular word is present
in the phrase.

e.g. Column A Column B
Johnny Passed the exam If A1 contains Johnny,
write 1 else 2
Rita missed the train If A1 contains Johnny,
write 1 else 2

How do I achieve the formula for column B?
Thanks in advance.

If I were doing it for myself then I would use a macro. If you want a macro
then let me know. Otherwise you could use 35 columns; one for each word and
then sum the values in the next column. If sum of values greater than 35 then
one of the words exists.

--
Regards,

OssieMac


"Malik" wrote:

thanks.
wt shud i do if I have more than one words to search for? I know I can use
IF as many times as the words but the words that I have to search for are
around 35.

I want to search that "is any of these 35 words present in the phrase in A2?
If yes, condition1, if no condition2".

Your help is much appreciated.

Thanks again.

"OssieMac" wrote:

Try the following:-

=IF(ISERROR(FIND("Johnny",A1,1)),"2", "1")

FIND is case sensitive. Use SEARCH in lieu as follows for non case sensitive:-

=IF(ISERROR(SEARCH("johnny",A3,1)),"2", "1")

--
Regards,

OssieMac


"Malik" wrote:

I have cells that have phrases written in them. Now I wish to assign a value
to the corresponding cell in the next column if a particular word is present
in the phrase.

e.g. Column A Column B
Johnny Passed the exam If A1 contains Johnny,
write 1 else 2
Rita missed the train If A1 contains Johnny,
write 1 else 2

How do I achieve the formula for column B?
Thanks in advance.