Excel (bless it's intelligence!) is evaluating =1 and <=12 as a TIME!

There are situations when this behavior can be leveraged to one's advantage
and then there are situations (like this one) where this behavior can bite
you in the butt!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Hmmm...

That's strange and right now I don't have an explanation for it.

I't's even more strange. Assume the string is:

X 5 at Y n a

If n = 0 it works.
If n =1 and <=11 it returns an incorrect decimal result.
If n = 12 it returns 0
If n 12 it works
If a < a it works

<time spent testing

Ok, I figured it out and it's freaking retarded!

Excel (bless it's intelligence!) is evaluating =1 and <=12 as a TIME!

Consider this string:

X 5 at Y 1 a

The result is 0.041666667 which is equivalent to 1:00 AM.

Here's how Excel arrives at this. When the formula steps through the MID
function and evaluates the strings based on ROW(INDIRECT(...)) you get an
array like this:

"1"
"1 "
"1 a"
"1 a"
"1 a"
"1 a"

Then the "--" coerces these to numbers and in doing so "1 a" (1:00 AM)
becomes 0.041666667 and LOOKUP returns the last value that is less than
the lookup_value so the result is the last instance of "1 a" or
0.041666667.

So, as long as the next character after the last number that is <12 < "a"
it will work!


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
It probably won't matter for the OP's posted usage, but I noticed that if
the text after the space that is after the last number starts with "a",
the number returned is not correct. For example, if the text in A1 were
this...

Carolina 3 at Pittsburgh 4 away

then your formula returns 0.166666666666667 as an answer.

Rick


"T. Valko" wrote in message
...
I'm willing to bet there is a shorter solution available

=LOOKUP(1000,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3, 4,5,6,7,8,9},SEARCH("at",A1))),ROW(INDIRECT("1:255 "))))

Even shorter (but array entered) using defined names:

Nums: ={0,1,2,3,4,5,6,7,8,9}
Length: =ROW(INDIRECT("1:255"))

=LOOKUP(1000,--MID(A1,MIN(FIND(Nums,A1&Nums,SEARCH("at",A1))),Len gth))

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
Sorry, small revision... I forgot about there may be nothing after the
number. Try this formula instead of the one I posted earlier...

=LEFT(MID(MID(A1,SEARCH(" at
",A1)+1,255),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1 &" ",SEARCH(" at
",A1&" ")+1,255)&{0,1,2,3,4,5,6,7,8,9})),255),FIND(" ",MID(MID(A1&"
",SEARCH(" at
",A1)+1,255),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1 ,SEARCH(" at
",A1)+1,255)&{0,1,2,3,4,5,6,7,8,9})),255))-1)

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
It's ugly (and I'm willing to bet there is a shorter solution
available), but the following formula will return the last number in
A1 as long as the work "at" appears in the text...

=LEFT(MID(MID(A1,SEARCH(" at
",A1)+1,255),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1 ,SEARCH(" at
",A1)+1,255)&{0,1,2,3,4,5,6,7,8,9})),255),FIND (" ",MID(MID(A1,SEARCH("
at ",A1)+1,255),MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1 ,SEARCH(" at
",A1)+1,255)&{0,1,2,3,4,5,6,7,8,9})),255))-1)

Rick


"Jambruins" wrote in message
...
In cell B239 I have the following text
Carolina 3 at Pittsburgh 4 (SO)

All of column B is like this except with different team names.

I would like a formula to pull out the number 4. Any idea how to do
this?
I have been trying to use the right command with the find command but
I can't
quite get it. Thanks.