I'm trying to find a formula to search for specific words in a line of text
and show the found word in a single cell.

For example the formula would search for "Joe" and "Mary" and "Steve" in a
single line of text. The text, to be filtered, might be as follows (The
students name is Mary.) The result of the search would have found "Mary" and
now show "Mary" in the cell. If the text to be filtered would have said (The
students name is Steve) the cell would show "Steve".

Does anyone know how to do what I'm trying to do?
Garth

Hi Garth,

With the string to search in A1, on one line -

=IF(ISERROR(FIND("Joe",A1)),IF(ISERROR(FIND("Mary" ,A1)),IF(ISERROR(FIND("Ste
ve",A1)),"not found","Steve"),"text Mary"),"text Joe")

Probably easier to put "Text " in another cell, say A2, and refer to it
....,A2 & "Joe")

Regards,
Peter T


"Garth the detailer" wrote in
message ...
I'm trying to find a formula to search for specific words in a line of
text
and show the found word in a single cell.

For example the formula would search for "Joe" and "Mary" and "Steve" in a
single line of text. The text, to be filtered, might be as follows (The
students name is Mary.) The result of the search would have found "Mary"
and
now show "Mary" in the cell. If the text to be filtered would have said
(The
students name is Steve) the cell would show "Steve".

Does anyone know how to do what I'm trying to do?
Garth

I think this is going to work, thanks much.

"Peter T" wrote:

Hi Garth,

With the string to search in A1, on one line -

=IF(ISERROR(FIND("Joe",A1)),IF(ISERROR(FIND("Mary" ,A1)),IF(ISERROR(FIND("Ste
ve",A1)),"not found","Steve"),"text Mary"),"text Joe")

Probably easier to put "Text " in another cell, say A2, and refer to it
....,A2 & "Joe")

Regards,
Peter T


"Garth the detailer" wrote in
message ...
I'm trying to find a formula to search for specific words in a line of
text
and show the found word in a single cell.

For example the formula would search for "Joe" and "Mary" and "Steve" in a
single line of text. The text, to be filtered, might be as follows (The
students name is Mary.) The result of the search would have found "Mary"
and
now show "Mary" in the cell. If the text to be filtered would have said
(The
students name is Steve) the cell would show "Steve".

Does anyone know how to do what I'm trying to do?
Garth

Peter, that worked like a charm. The only thing I question is if I add a
seventh name to be found in the formula an error pops up. It works great for
the first six names but seven is a no go. Is there a limit to how many names
I can search for?

forever grateul
Garth

"Peter T" wrote:

Hi Garth,

With the string to search in A1, on one line -

=IF(ISERROR(FIND("Joe",A1)),IF(ISERROR(FIND("Mary" ,A1)),IF(ISERROR(FIND("Ste
ve",A1)),"not found","Steve"),"text Mary"),"text Joe")

Probably easier to put "Text " in another cell, say A2, and refer to it
....,A2 & "Joe")

Regards,
Peter T


"Garth the detailer" wrote in
message ...
I'm trying to find a formula to search for specific words in a line of
text
and show the found word in a single cell.

For example the formula would search for "Joe" and "Mary" and "Steve" in a
single line of text. The text, to be filtered, might be as follows (The
students name is Mary.) The result of the search would have found "Mary"
and
now show "Mary" in the cell. If the text to be filtered would have said
(The
students name is Steve) the cell would show "Steve".

Does anyone know how to do what I'm trying to do?
Garth

I see Ron Ronsenfeld has already posted an excellent suggestion in your to
other thread. Adapting his formula slightly, try -

=A2 & INDEX(namelist,MATCH(FALSE,ISERR(FIND(namelist,A1) ),0))

Array entered

A1 - the search string
A2 - the text that prefixes the find result
namelist - a ref or named range of your find strings, Joe, Mary, Steve, etc

I'm pleased Ron flagged your other thread. Otherwise I would have spent a
lot of time trying to find a solution, probably ending up with a statement
about 7 nested If's and/or formula too long.

Regards,
Peter T




"Garth" wrote in message
...
Peter, that worked like a charm. The only thing I question is if I add a
seventh name to be found in the formula an error pops up. It works great
for
the first six names but seven is a no go. Is there a limit to how many
names
I can search for?

forever grateul
Garth

"Peter T" wrote:

Hi Garth,

With the string to search in A1, on one line -


=IF(ISERROR(FIND("Joe",A1)),IF(ISERROR(FIND("Mary" ,A1)),IF(ISERROR(FIND("Ste
ve",A1)),"not found","Steve"),"text Mary"),"text Joe")

Probably easier to put "Text " in another cell, say A2, and refer to it
....,A2 & "Joe")

Regards,
Peter T


"Garth the detailer" wrote
in
message ...
I'm trying to find a formula to search for specific words in a line of
text
and show the found word in a single cell.

For example the formula would search for "Joe" and "Mary" and "Steve"
in a
single line of text. The text, to be filtered, might be as follows
(The
students name is Mary.) The result of the search would have found
"Mary"
and
now show "Mary" in the cell. If the text to be filtered would have
said
(The
students name is Steve) the cell would show "Steve".

Does anyone know how to do what I'm trying to do?
Garth

try this to search cell e2 for the name in c2
=IF(ISERR(SEARCH(C2,E2)),"",C2)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Garth the detailer" wrote in
message ...
I'm trying to find a formula to search for specific words in a line of
text
and show the found word in a single cell.

For example the formula would search for "Joe" and "Mary" and "Steve" in a
single line of text. The text, to be filtered, might be as follows (The
students name is Mary.) The result of the search would have found "Mary"
and
now show "Mary" in the cell. If the text to be filtered would have said
(The
students name is Steve) the cell would show "Steve".

Does anyone know how to do what I'm trying to do?
Garth

On Mon, 13 Aug 2007 13:22:03 -0700, Garth the detailer
wrote:

I'm trying to find a formula to search for specific words in a line of text
and show the found word in a single cell.

For example the formula would search for "Joe" and "Mary" and "Steve" in a
single line of text. The text, to be filtered, might be as follows (The
students name is Mary.) The result of the search would have found "Mary" and
now show "Mary" in the cell. If the text to be filtered would have said (The
students name is Steve) the cell would show "Steve".

Does anyone know how to do what I'm trying to do?
Garth

See another answer in .misc

Please don't multipost.
--ron