problems with search and fill string

[SPONELLE]

Have one field which is quite large and includes location, part of the sting is # BOT #, the BOT is a site name and changes from row to row. eg. # TRA # or # MOR # ,etc.

I have a string below which can find one off location and provide entry into a separate cell (see F1 below).

=IF(SUM(ISNUMBER(SEARCH({"# BOT #"},$E2))+0),"BOT","")

The search F2 below can find all of the different sites, but will not fill with the same letter string as found.

=IF(SUM(ISNUMBER(SEARCH({"# *** #"},$E2))+0),"BOT","")

i.e. # MOR # returns MOR, # TRA # returns TRA, etc

Any ideas?

 

[Aladin Akyurek]

Have one field which is quite large and includes location, part of the sting is # BOT #, the BOT is a site name and changes from row to row. eg. # TRA # or # MOR # ,etc.

I have a string below which can find one off location and provide entry into a separate cell (see F1 below).

=IF(SUM(ISNUMBER(SEARCH({"# BOT #"},$E2))+0),"BOT","")

The search F2 below can find all of the different sites, but will not fill with the same letter string as found.

=IF(SUM(ISNUMBER(SEARCH({"# *** #"},$E2))+0),"BOT","")

i.e. # MOR # returns MOR, # TRA # returns TRA, etc

Any ideas?

=IF(SUM(ISNUMBER(SEARCH("# *** #",$E2))+0),TRIM(MID($E2,SEARCH({"# *** #"},$E2)+1,4)),"")