ISNUMBER(SEARCH....problem

[doris_day]

I'm doing a Search using the Isnumber routine and its working fine. However, I'm searching the Reverse Forecast cells with strings like 2 -10/ 10 - 2. When I do a seach for "1" say, it comes up positive with the above string because it sees a "1" in the "10". How do I make sure it only shows positive when it sees the 'whole' "10" rather than just the "1" ?

If you get my drift.....

[doris_day]

Just to make sure you understand my meaning, when I search for "1" I want a "10" to show as negative and not positive.....

[osknows]

I'm not exactly sure what your trying to achieve from your explanation or how you're using ISNUMBER or SEARCH but this may help...

Say you have strings "10 - 2" or "2 - 10" in cell A1

you could find the position of the - using
=FIND("-",A1) which would return 4 and 3 for the two above strings

then for string "10 - 2"
=LEFT(A1,FIND("-",A1)-1)*1 returns 10
=RIGHT(A1,LEN(A1)-FIND("-",A1)+1)*1 returns -2

and for string "2 - 10"
=LEFT(A1,FIND("-",A1)-1)*1 returns 2
=RIGHT(A1,LEN(A1)-FIND("-",A1)+1)*1 returns -10

If that doesn't do what you need give some specific examples of the strings and what you want returned

Os

[doris_day]

OK, thanks for the reply. Not quite what I need and that's probably because I didn't explain it well.
On one sheet I have the Saddle Cloth number, which say is '1'. In another sheet I want to search in column A for those instances when '1' is seen. I use this routine in each cell in a particular column:

=IF(ISNUMBER(SEARCH('RevFcst Calc'!$B$12,'BF RevFcst'!A17,1)),"Y","")

So, the number '1' is the number in cell 'RevFcst Calc'!$B$12 and in this case I am searching in cell 'BF RevFcst'!A17 which looks like 2 -10 / 10 -2

In this case its returning "Y" because it sees a '1' in the '10'. I want it to return "" in this case because, although there is a '1' in '10', '1' is the whole number I'm looking for....

Does this make it more understandable ?

[Captain Sensible]

Probably be better off using FIND as ISNUMBER will always pick out the 1 in 10 and it'd be hassle to code any exceptions. Using FIND you can utilise the fact there are spaces within the way the data is sent so you can differentiate from 1 and 10 in the reverse forecasts i.e 1 is sent "1 " 10 is sent "10 " whereas the saddlecloth number is just sent as the exact number.

If that makes any sense to you

[doris_day]

Thanks for the advice. I'll take a look at the FIND function and see where I get to

[osknows]

Assuming your string always seperates numbers with a space then you could use

Data is in A1 & A2
A1 = 1
A2 = "2 -10 / 10 -2"

recode A2 as
=SUBSTITUTE(A2&"-"," ","-") to give "2--10-/-10--2--"

then recode the test string as =A1&"-" to give "1-"

Then search for "1-" in "2--10-/-10--2--" using

=IF(ISERROR(FIND(A1&"-",SUBSTITUTE(A2&"-"," ","-"),1)),"","Y")

returns Y if 1- is in string or nothing if not. So for example above returns nothing

[doris_day]

I like that and will give that a try too....thanks for the ideas...

[Captain Sensible]

Sorry meant to say try SEARCH on it's own just append the space into the search using &

=SEARCH(A4&" ",A3)

[osknows]

Just saw earlier post by CS and rather than substitute the space actually use it

so formula is

=IF(ISERROR(FIND(A1&" ",A2,1)),"","Y")

[Captain Sensible]

Try

=IF(SEARCH('RevFcst Calc'!$B$12&" ",'BF RevFcst'!A17,1),"Y","")

[Ian]

Adding the space might not work if actually looking for 10 or more ?

[Ian]

Sorry it will work, but Betfair miss the space out for 1 & 10 eg. 1- 10 / 10 - 1 so in this case it won't find the "1"

[Captain Sensible]

osknows'

=IF(ISERROR(FIND(A1&" ",A2,1)),"","Y") is best to use as it'd trap the error also.

Ian the space is in the reverse forecast cell so if 10 was in the saddlecloth it'd be picked up as "10 "

[doris_day]

Wow, this forum is truly blessed with some good guys....thanks a lot.....