Finding different words in a cell

[JRichardson]

I have a spreadsheet that has several columns & rows. One column contains
the type of program the customer is on (ie., gold, silver, bronze) although
it is not spelled out that simply - it is Progam 1 Gold 09, etc. I am
trying to create a formula that will only return the words gold, silver or
bronze. I have tried using Mid & Find, but can only make that work for one
of the programs. Any help would be appreciated. thanks.

 

[Mike H]

Hi,

If all of the strings are the same format

Progam 1 Gold 09
Progam 1 Silver 09
Progam 1 Bronze 09

then try this formula

=MID(MID(MID(SUBSTITUTE(A1," ","^",2),1,256),FIND("^",SUBSTITUTE(A1,"
","^",2)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1,"
","^",2),1,256),FIND("^",SUBSTITUTE(A1," ","^",2)),256))-2)

Mike

 

[Sean Timmons]

If all programs are of one of these types, then:

=IF(ISERROR(SEARCH("Bronze",A2)),IF(ISERROR(SEARCH("Silver",A2)),"Gold","Silver"),"Bronze")

else, you'll have to add another if to search for Bronze and return, say, ""
if it's an error.

 

[T. Valko]

Try this:

=LOOKUP(1E100,SEARCH({"gold","silver","bronze"},A1),{"gold","silver","bronze"})

Or, list the search words in a range of cells:

C1 = gold
C2 = silver
C3 = bronze

=LOOKUP(1E100,SEARCH(C$1:C$3,A1),C$1:C$3)