Search Formula Help

Literae

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

Literae

New Member
Joined
May 11, 2018
Messages
10
Hi Fluff!

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

Fluff

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

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top