ISNUMBER(SEARCH....problem

[Captain Sensible]

I didn't know that Ian never use the fcast stuff myself, I guess you'd have to use an OR statement in there too if it was needed to include something like A1&"-" to catch those

[osknows]

Sorry it should be
=IF(ISERROR(FIND(A1&" ",A2&" ",1)),"","Y")

To pick up if there is a 1 on the end of the string

[doris_day]

Looks as though there is a space between all numbers so it looks like 1 - 10 / 10- 1 for instance

[doris_day]

or rather 1 - 10 / 10 - 1

[Ian]

Are you sure, Doris ? Have a look at the 4:40 at Yarmouth tomorrow. Perhaps the format is variable depending on who set sets the market up ?

[doris_day]

This version works fine:

=IF(ISNUMBER(SEARCH('RevFcst Calc'!$C$12&" ",'BF RevFcst'!A6,1)),"Y","")

Certainly in the cases I've looked at there is always a space between the numbers in column A on the RevFcst worksheet

[GaryRussell]

Here's my attempt. I always revert to VBA

Insert the following code into a module. You can then use the formula =containsNumber(A5,"1") etc.

Code: Select all

Public Function containsNumber(r As Range, s As String) As Boolean
    containsNumber = False
    p = InStr(r.Value, s)
    If p <> 0 Then
        If p + (Len(s) - 1) = Len(r.Value) Then
            containsNumber = True
        Else
            If Not IsNumeric(Mid(r.Value, p + Len(s), 1)) Then containsNumber = True
        End If
    End If
End Function


[doris_day]

Excellent. That works too !!

Make me proud to be part of the forum, to be surrounded by such bright and generous people. Just what a good forum should be.

Thanks again....