Identifying if a cell contains specific text

Hi All - wondering if someone knows how to do the following (its possible i've banged my head against a wall one too many times trying to figure this out!):

I have a column of data with a lot of garbled text:

CelebrityCruises_Indulged_728x90.swf
Celebrity_EachDay_160x600_v02_v3.jpg
Celebrity_alt_EachDay_120x600_v03.swf
CelebrityCruises_EachDay_160x600.swf

Within each of these entries is the name of the creative type (either "indulged" or "eachday"), however unfortunately the creative name isn't in a uniform position within the cell. (for instance it isn't 10 positions at the start of the text or 10 positions from the end). In a cell adjacent to each entry, i want to label the entry as either "indulged" or "eachday" so that i can pivot out information by creative type. Does anyone have any suggestions? I'm hoping there is a formula i can use within the worksheet (maybe along the lines of: =if(A2 contains "indulged", indulged,...etc)) since i have little to no expertise with VB. That said, if VB is the only way, i suppose i'll have to get a little friendlier with it.

I have access to both office 2007 and 2003.

Huge thanks for any help here!!

ideally i'd like to tackle this with formulas, but not sure if is possible to do so.

already, i like your thought process! this is something i'll use in the future, but unfortunately with this task there is little to no uniformity in the structure of the text. for instance, some of the entries list the campaign name at the start of the text (Eachday728x90_revised.swf), some fail to separate the name with underscores entirely (previous example works here as well). Is there a way to simply say, (1) look for "indulge" or "eachday" etc (there are more campaigns) anywhere in this cell and (2) if it appears, produce it here?.

you can say look fro "Indulged" in the find statement and it will return a number if it finds it and err if not.

i would tag in a lower cast on the filename as the find will be case sensitive. this method will only work with 8 nested IF's mind. granted you can uses extra columns to get another 8 if needed.

=IF(ISNUMBER(FIND("indulged",LOWER(A3),1)),"Indulged",IF(ISNUMBER(FIND("eachday",LOWER(A3),1)),"EachDay","No"))

Here are 2 other possibilities

=IF(COUNTIF(A1,"*indulged*"),"Indulged",IF(COUNTIF(A1,"*eachday*"),"Eachday",""))

=LOOKUP(2^15,SEARCH({"eachday","indulged"},A1),{"eachday","indulged"})

The second is particularly useful if you have many more than 2 items to search for because you can use a cell range to simplify, e.g. with "Indulged" in J1 and "eachday" in J2

=LOOKUP(2^15,SEARCH($J$1:$J$2,A1),$J$1:$J$2)

Note, if more then 1 of the terms appears this formula will return the lowest in the list, e.g. if J1 and J2 appear then it returns J2

The second formula is exactly what i was looking for - huge thanks!! This eliminates a good hours worth of work each week

Wondering what the "2^15" represents in this formula - i haven't seen the lookup value term used like this before.

In this type of formula the lookup value [2^15] needs to be a value which is larger than any number that might appear in the range. Sometimes you could see 9.99999999999999E+307 or similar but here the SEARCH function returns the position of the found text, and as no cell can contain more than 2^15-1 [32767] characters then 2^15 fulfills the requirement.... probably something like

=LOOKUP(1000,SEARCH($J$1:$J$2,A1),$J$1:$J$2)

would be sufficient as long as A1 has less than 1000 characters, or even

=LOOKUP(LEN(A1)+1,SEARCH($J$1:$J$2,A1),$J$1:$J$2)