How can I check cells for number series?

Posted by Stephen Younger on Super User See other posts from Super User or by Stephen Younger
Published on 2012-12-04T13:28:14Z Indexed on 2012/12/04 17:10 UTC
Read the original article Hit count: 158

Filed under:
|
|
|

I have a bit of a problem evaluating an excel cell. Example:

          M   M   M   M   M   M   M   M   M   
          1   2   3   4   5   6   7   8   9
2;5;7
1;9
3;5;7;9   

I have a number of excel cells which contain numbers (months). In the first column I have a series of numbers. I want to use conditional formatting to color the corresponding cells in the right columns. If correctly colored I would get something like this:

          M   M   M   M   M   M   M   M   M   
          1   2   3   4   5   6   7   8   9
2;5;7         X           X       X
1;9       X                               X
3;5;7;9           X       X       X       X

The formula I have now is this:

IF(ISNUMBER(FIND(L$22;$K23));$H23;"")

but the problem is that cells are colored too which contain part of a number. If I enter 10;15 as input I get this:

          M   M   M   M   M   M   M   M   M   M   M   M   M   M   M
          1   2   3   4   5   6   7   8   9  10  11  12  13  14  15
10;15     X               X                   X                   X

because 1 and 5 are found too. I only want column 10 and 15 to be marked. How can I change the formula or the input?

© Super User or respective owner

Related posts about excel-2007

Related posts about formatting