asked on
Excel - Search string for any one of multiple values
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"
What is a better way to do this?
Thanks in advance.
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"
What is a better way to do this?
Thanks in advance.
Microsoft Excel
Last Comment
rspahitz
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks!
That may not work
=IF(OR(ISNUMBER(SEARCH("20
In above there are only 2 conditions.
I would suggest add one condition at a time and make sure it is working.
HTH
Ashok
=IF(OR(ISNUMBER(SEARCH("20
In above there are only 2 conditions.
I would suggest add one condition at a time and make sure it is working.
HTH
Ashok
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":
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","")
Here is 5 conditions.
=IF(OR(ISNUMBER(SEARCH("20
Above is tested OK.
Thanks
=IF(OR(ISNUMBER(SEARCH("20
Above is tested OK.
Thanks
=IF(OR(ISNUMBER(SEARCH("20
You can do this with one SEARCH function like this
=IF(COUNT(SEARCH({2000,200
or even shorter like this:
=IF(COUNT(SEARCH(2000+{0,1
regards, barry
=IF(COUNT(SEARCH({2000,200
or even shorter like this:
=IF(COUNT(SEARCH(2000+{0,1
regards, barry
Good point Barry.
I guess we can even shorten it to this:
=IF(ISERR(SEARCH({"200?","
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?
I guess we can even shorten it to this:
=IF(ISERR(SEARCH({"200?","
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?
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
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?","
...or go back to OR with ISNUMBER, i.e.
=IF(OR(ISNUMBER(SEARCH({"2
All of those should return the correct result assuming 2000 to 2010 only are "bad years"
regards, barry
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
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?","
...or go back to OR with ISNUMBER, i.e.
=IF(OR(ISNUMBER(SEARCH({"2
All of those should return the correct result assuming 2000 to 2010 only are "bad years"
regards, barry
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?","
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?
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?","
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?
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,200
or
=IF(COUNT(SEARCH(2000+{0,1
I prefer this as you can easily see all values.
=IF(COUNT(SEARCH({2000,200
or
=IF(COUNT(SEARCH(2000+{0,1
Good Year 2014 is coming soon.
>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?","
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
if I add an AND as per my suggestion above, i.e.
=IF(AND(ISERR(SEARCH({"200
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
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?","
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
if I add an AND as per my suggestion above, i.e.
=IF(AND(ISERR(SEARCH({"200
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
ASKER
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!
Thanks to the other experts for chiming in!
That's good to know!
Barry, you're correct...I just wasn't looking carefully enough.