how can I search a field for a particular word
 

I have a large document where in column C contains a sentence. I need a
formula that will look in Column C, identify the word "USDA" and populate a
new column with a 1. Can anyone help me with this?

Assume data in C2 down

If it's to be case sensitive,

Put in say, D2:
= IF(ISNUMBER(FIND("USDA",C2)),1,"")
Copy D2 down as far as required

Replace FIND with SEARCH if it's to be case insensitive
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"brantty" wrote in message
...
I have a large document where in column C contains a sentence. I need a
formula that will look in Column C, identify the word "USDA" and populate
a
new column with a 1. Can anyone help me with this?

Hi!

Try this:

=IF(ISNUMBER(SEARCH("USDA",C1)),1,"")

Biff

"brantty" wrote in message
...
I have a large document where in column C contains a sentence. I need a
formula that will look in Column C, identify the word "USDA" and populate
a
new column with a 1. Can anyone help me with this?

that worked. Now not to complicate it, but can I also search for two words in
the same cell but not in any order, just "USDA" and "GRADE" anywhere in the
sentence?

"Max" wrote:

Assume data in C2 down

If it's to be case sensitive,

Put in say, D2:
= IF(ISNUMBER(FIND("USDA",C2)),1,"")
Copy D2 down as far as required

Replace FIND with SEARCH if it's to be case insensitive
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"brantty" wrote in message
...
I have a large document where in column C contains a sentence. I need a
formula that will look in Column C, identify the word "USDA" and populate
a
new column with a 1. Can anyone help me with this?

Think we could try instead in D2:

= IF(AND(ISNUMBER(FIND("USDA",C2)),ISNUMBER(FIND("GR ADE",C2))),1,"")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"brantty" wrote in message
...
that worked. Now not to complicate it, but can I also search for two words
in
the same cell but not in any order, just "USDA" and "GRADE" anywhere in
the
sentence?

=IF(AND(ISNUMBER(SEARCH({"USDA","GRADE"},C1))),1," ")

Biff

"Max" wrote in message
...
Think we could try instead in D2:

= IF(AND(ISNUMBER(FIND("USDA",C2)),ISNUMBER(FIND("GR ADE",C2))),1,"")

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"brantty" wrote in message
...
that worked. Now not to complicate it, but can I also search for two
words
in
the same cell but not in any order, just "USDA" and "GRADE" anywhere in
the
sentence?

Thanks for refinement, Biff !
Much neater ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Biff" wrote in message
...
=IF(AND(ISNUMBER(SEARCH({"USDA","GRADE"},C1))),1," ")

Biff

You can use the same syntax for an OR situation:

=IF(OR(ISNUMBER(SEARCH({"USDA","GRADE"},C1))),1,"" )

Biff

"Max" wrote in message
...
Thanks for refinement, Biff !
Much neater ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Biff" wrote in message
...
=IF(AND(ISNUMBER(SEARCH({"USDA","GRADE"},C1))),1," ")

Biff

Thanks for the extended illustration, Biff !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Biff" wrote in message
...
You can use the same syntax for an OR situation:

=IF(OR(ISNUMBER(SEARCH({"USDA","GRADE"},C1))),1,"" )

Biff