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.