Finding different words in a cell

  • Thread starter Thread starter JRichardson
  • Start date Start date
J

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.
 
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
 
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.
 
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)
 
Back
Top