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.

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

"JRichardson" wrote:

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.

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.

"JRichardson" wrote:

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.

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)

--
Biff
Microsoft Excel MVP


"JRichardson" wrote in message
...
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.