sounds easy but I can't figure it out.....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
I think this is going to work, thanks much.

Peter T said:
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
Click to expand...
 
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
 
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
Click to expand...

See another answer in .misc

Please don't multipost.
--ron
 
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
 
Back
Top