You could just look at the first x number of characters:

=if(left(c2,7)="roadway","A_",if(left(c2,9)="eleva tion","B_", ....



bridgemonkey wrote:

Similar situation sort of:
I have a column with photo captions. If the caption contains the word
"Roadway", I need a "A_" returned in adjacent column, If the caption contains
the word "Elevation", I need "B_" returned in adjacent column. Same with
"General" to "C_". By the way, the "key" word will always start with
character 1, hence the "=1". The problem I'm having is when "Roadway" does
not exist, the value returned is "#VALUE" which apparently cannot be
evaluated and doesn't seem to hit the second IF .... never seen ISERROR
before so maybe that will work,

This is what I have so far:
=IF(SEARCH("Roadway",C2,1)=1,"A_",IF(SEARCH("Eleva tion",C2,1)=1,"B_",IF(SEARCH("Elevation",C2,1)=1," C_","")))

Thanks in advance,

"Gerry-W" wrote:


the way I normally do this is to simplify the formula by creating a list
elsewhere. In a different tab in the same worksheet list the words you
are looking for.

Then in the worksheet you desire the output use the vlookup function:

=vlookup(lookupcell,listofwords,1,false)

now this will give you an error code if the word is not there and give
the actual word when it is there. Therefore to further refine:

=if(iserror(vlookup(lookupcell,listofwords,1,false )),"","*")

That should do the trick.

Alternatively you can use the OR fuction:

=if(OR(a1="This",a1="That",a1="Anything"),"*","")

Obviously if the list is small then OR will do the trick otherwise I
reccomend vlookup using a list.


--
Gerry-W


--

Dave Peterson