Search Formula Help

Literae

New Member
Joined
May 11, 2018
Messages
9
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,354
Office Version
365
Platform
Windows
How about
=IF(ISNUMBER(SEARCH("AWAITING PART",LEFT(O2,20))),"PARTS","")

change the number in red to suit
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,354
Office Version
365
Platform
Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,354
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,078,094
Messages
5,338,193
Members
399,212
Latest member
Braincraft

Some videos you may like

This Week's Hot Topics

Top