I would make one suggested modification though... change your SEARCH to be
for "_at_"

Yep, good point!

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
See... I knew that had to be better approach than the
bull-in-the-china-shop approach I used.<g Thanks for posting it. I would
make one suggested modification though... change your SEARCH to be for
"_at_" (that is, <spaceat<space) on the off chance that some of the
other words in the text have "at" embedded within them.

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.