View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.misc
Jambruins Jambruins is offline
external usenet poster
 
Posts: 65
Default find a number in a cell

Rick,
That works for the cells that have (SO) or (OT) at the end but it gives me
a #value! if the cells end with a number. Any ideas? Thanks.

"Rick Rothstein (MVP - VB)" wrote:

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.