Search Formula Help

Literae

New Member
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
=IF(ISNUMBER(SEARCH("AWAITING PART",LEFT(O2,20))),"PARTS","")

change the number in red to suit

Fluff

MrExcel MVP, Moderator
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

New Member
Hi Fluff!

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

Fluff

MrExcel MVP, Moderator
You're welcome & thanks for the feedback

1,081,981
Messages
5,362,533
Members
400,679
Latest member
alecalec202

This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...