Solved

Excel - Search string for any one of multiple values

Posted on 2013-12-30
16
2,415 Views
Last Modified: 2013-12-30
Experts, I have a column (Column "O") of text strings in excel and the text in some rows contains a year e.g. 2000.
Years could be anything from 2000 to 2010.

What I want to do is write a formula in an adjacent column (Column "N") that will search the text strings in Col O for the occurrence of any of a number of designated year values and if one of those values appears, write something like "Bad Year" in the adjacent column and if not a match, leave the cell blank.

Here's what I tried but excel does not like it:

=IF(ISNUMBER(SEARCH("2000",o2)) OR ISNUMBER(SEARCH("2001",o2)) OR ISNUMBER(SEARCH("2002",o2)) OR ISNUMBER(SEARCH("2003",o2)) OR ISNUMBER(SEARCH("2004",o2)) OR ISNUMBER(SEARCH("2005",o2)) OR ISNUMBER(SEARCH("2006",o2)) OR ISNUMBER(SEARCH("2007",o2)) OR ISNUMBER(SEARCH("2008",o2)) OR ISNUMBER(SEARCH("2009",o2)) OR ISNUMBER(SEARCH("2010",o2)) OR ISNUMBER(SEARCH("2011",o2)),"Bad Year","")

What is a better way to do this?

Thanks in advance.
0
Comment
Question by:bobinorlando
  • 7
  • 4
  • 3
  • +1
16 Comments
 
LVL 13

Accepted Solution

by:
Ashok earned 500 total points
ID: 39746453
When typing in notepad, make sure it is one continuous line (without a line feed).

=IF(((ISNUMBER(SEARCH("2000",O2)))*OR(ISNUMBER(SEARCH("2001",O2)))*OR(ISNUMBER(SEARCH("2010",O2)))*OR(ISNUMBER(SEARCH("2011",O2)))),"","Bad Year")

Add more 2002 to 2009 in above string.

HTH
Ashok
0
 
LVL 1

Author Comment

by:bobinorlando
ID: 39746458
Thanks!
0
 
LVL 13

Expert Comment

by:Ashok
ID: 39746474
That may not work

=IF(OR(ISNUMBER(SEARCH("2000",O3)),ISNUMBER(SEARCH("2000",O3))),"","Bad Year")

In above there are only 2 conditions.

I would suggest add one condition at a time and make sure it is working.

HTH
Ashok
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:rspahitz
ID: 39746475
How about using wildcards like this?
SEARCH("20??",O2)

It will actually give you more than you need, so maybe that's not the way you want to go...
but you could also extend it to "200?" or "2010":

=IF(OR(ISNUMBER(SEARCH("200?",O2)), ISNUMBER(SEARCH("2010",O2))), "Bad Year","")

Open in new window

0
 
LVL 13

Expert Comment

by:Ashok
ID: 39746482
Here is 5 conditions.

=IF(OR(ISNUMBER(SEARCH("2000",O3)),ISNUMBER(SEARCH("2001",O3)),ISNUMBER(SEARCH("2002",O3)),ISNUMBER(SEARCH("2003",O3)),ISNUMBER(SEARCH("2004",O3))),"","Bad Year")

Above is tested OK.

Thanks
0
 
LVL 13

Expert Comment

by:Ashok
ID: 39746501
=IF(OR(ISNUMBER(SEARCH("2000",O2)),ISNUMBER(SEARCH("2001",O2)),ISNUMBER(SEARCH("2002",O2)),ISNUMBER(SEARCH("2003",O2)),ISNUMBER(SEARCH("2004",O2)),ISNUMBER(SEARCH("2005",O2)),ISNUMBER(SEARCH("2006",O2)),ISNUMBER(SEARCH("2007",O2)),ISNUMBER(SEARCH("2008",O2)),ISNUMBER(SEARCH("2009",O2)),ISNUMBER(SEARCH("2010",O2)),ISNUMBER(SEARCH("2011",O2))),"","Bad Year")
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39746866
You can do this with one SEARCH function like this

=IF(COUNT(SEARCH({2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011},O2)),"Bad Year","")

or even shorter like this:

=IF(COUNT(SEARCH(2000+{0,1,2,3,4,5,6,7,8,9,10,11},O2)),"Bad Year","")

regards, barry
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39746890
Good point Barry.

I guess we can even shorten it to this:

=IF(ISERR(SEARCH({"200?","2010"},O2)),"","Bad year")

Oh Bob, I see in your post that it seems that you want 2000-2010 to be "bad year" (which is how my code works).  Is that correct or are those the "good years"?
Also, part of your post says to use the range 2000-2010 but your code seems to include 2011.  I guess that's easy enough to adopt, but which one is correct?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39746934
That's not going to work, as is, Rob!

When you search for multiple values in the { and } braces SEARCH will return an array and so will ISERR, but in your suggested formula you have no function that handles that array, so it works for 2003 but not 2010.

You can use AND to handle that, i.e.

=IF(AND(ISERR(SEARCH({"200?","2010"},O2))),"","Bad Year")

In my version COUNT is different, that function returns a single result from an array, so you can use this version again with COUNT:

=IF(COUNT(SEARCH({"200?","2010"},O2)),"Bad Year","")

...or go back to OR with ISNUMBER, i.e.

=IF(OR(ISNUMBER(SEARCH({"200?","2010"},O2))),"Bad Year","")

All of those should return the correct result assuming 2000 to 2010  only are "bad years"

regards, barry
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39746967
thanks Barry...not sure why it works fine on my machine (Excel 2007), but COUNT seems like a worthwhile alternative.

Here's the data I'm testing in A1:A20 :
abc1996xyz
abc1997xyz
abc1998xyz
abc1999xyz
abc2000xyz
abc2001xyz
abc2002xyz
abc2003xyz
abc2004xyz
abc2005xyz
abc2006xyz
abc2007xyz
abc2008xyz
abc2009xyz
abc2010xyz
abc2011xyz
abc2012xyz
abc2013xyz
abc2014xyz
abc2015xyz

starting with this formula:
=IF(ISERR(SEARCH({"200?","2010"},A1)),"","Bad year")
B5:B15 correctly show a message and all the rest are empty.

Are you saying that it won't work if there are multiple matches?
0
 
LVL 13

Expert Comment

by:Ashok
ID: 39746982
By the way, first should be true which empty string and second is false so "Bad Year".

I prefer this as you can easily see all values.

=IF(COUNT(SEARCH({2000,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011},O2)),"","Bad Year")

or

=IF(COUNT(SEARCH(2000+{0,1,2,3,4,5,6,7,8,9,10,11},O2)),"","Bad Year")
0
 
LVL 13

Expert Comment

by:Ashok
ID: 39746997
Good Year 2014 is coming soon.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39747080
>B5:B15 correctly show a message and all the rest are empty.

Are you sure, Rob? I get B15 empty, although A15 contains 2010

That's what I would expect because in this formula

=IF(ISERR(SEARCH({"200?","2010"},A15)),"","Bad year")

Where A15 = abc2010xyz

You get SEARCH returning this array:

{#VALUE!,4}

and then when you apply ISERR to that array you get this array

{TRUE,FALSE}

....but as I said you have no function that evaluates that array so typically the IF function uses the first value, TRUE, so you get a blank instead of "bad year".

Your results are the same as if you used this formula

=IF(ISERR(SEARCH("200?",A1)),"","Bad year")

if I add an AND as per my suggestion above, i.e.

=IF(AND(ISERR(SEARCH({"200?","2010"},A1))),"","Bad Year")

the function it works as required  because

=AND({TRUE,FALSE}) = FALSE

All other years work because they are picked up by the "200?" criterion

regards, barry
0
 
LVL 1

Author Closing Comment

by:bobinorlando
ID: 39747368
Hmm I attempted to accept this as the solution earlier today. It didn't seem to take. Anyway I found Ashok's solution to work with a minor modification - I changed * to +.
Thanks to the other experts for chiming in!
0
 
LVL 13

Expert Comment

by:Ashok
ID: 39747487
That's good to know!
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39747578
Barry, you're correct...I just wasn't looking carefully enough.
0

Featured Post

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question