Search Formula Help

[Literae]

Bonjour!

I am trying to use a search formula to return a word if it finds certain words in a cell. So far so good, the formula is working how it is supposed to, but not how I want it. I need to limit the search range within a cell to lets say the first 20 characters. Please see example and formula below:

Formula:

=IF(ISNUMBER(SEARCH("AWAITING PART",O2)),"PARTS" ,IF(ISNUMBER(SEARCH("AWAITING PARTS",O2)),"PARTS",""))

O2:

18/11 Finding Commerical dealer to go to13/11 needs to go to Comm. dealer; emailed BG for preference07/11 still waiting for auth from Sopp and Sopp to move to dealer06/11 Awaiting auth from Sopp and Sopp to move vehicle to dealer05/11need to go to VW Commerical van centre for repair25/10 AWAITING PART FROM BRITISH GAS - NO ETA17/10 perspects window still missing17/10 parts arrived14/10 wrong parts14/10 parts here08/10 awaiting part

Problem:

The formula returns "PART" because it is found throughout the notes. However part has since arrived and I basically need to know the most recent note (at the start) if it still contains "Awaiting Part" or "Awaiting Parts".

Cheers in advance! (Hopefully my account is active now so this actually posts)

 

[Fluff]

How about
=IF(ISNUMBER(SEARCH("AWAITING PART",LEFT(O2,20))),"PARTS","")

change the number in red to suit

 

[Fluff]

Alternatively, you could use
=IF(ISNUMBER(SEARCH("AWAITING PART",LEFT(O2,SEARCH("/",O2&"/",5)))),"PARTS","")

Which will search up to the 2nd / in the string

 

[Literae]

Hi Fluff!

Perfect, it works! That has made my morning report so much easier to work with. Thanking you!

 

[Fluff]

You're welcome & thanks for the feedback