# How can I check cells for number series?

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?

-

Tampering with your input data may be a 'cheat' - but perhaps easiest! I'd add a semi colon at the start and end of your input data and then use

`=FIND(";"&L\$22&";",\$K23)>0`

though I think you would have to replace the comma in this formula with a semi colon also.

-
No need to change the source data. Use `=FIND(";"&L\$22&";",";"&\$K23&";")>0` (or in the local of the OP `=FIND(";"&L\$22&";";";"&\$K23&";")>0`) – chris neilsen Dec 5 '12 at 7:02
@chrisneilsen That’s a much better answer (ie adding the ‘extra’ delimiters ‘on the fly’). I’m going looking at some of your other answers for gems like that! – pnuts Dec 5 '12 at 8:42
Thanks for comments. – Stephen Younger Dec 5 '12 at 10:25

I found some VBA-function which I changed to this and it works:

``````Function CHECK_INTERVALL(str, colmonth, sepChar)

Dim months As Variant
months = Split(str, sepChar)
Dim i As Integer

CHECK_INTERVALL = False

For i = LBound(months) To UBound(months)
If CInt(colmonth) = CInt(months(i)) Then
CHECK_INTERVALL = True
End If
Next i

End Function
``````

If the current month is in the list I get true and the conditional formatting colors the cell.

-
Do you realise you may, indeed are encouraged, to switch hats (as this is the hat season!) and in your capacity as OP accept this (your own) answer? There should be an outlined tick visible to you that you can click on, to turn green. – pnuts Dec 22 '12 at 1:18