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.

Not all of the cells have the (SO) at the end either. Some of them end like
this
Carolina 3 at Pittsburgh 4

"Jambruins" wrote:

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.

Select the cells in column B and pull-down:

Data Text to Columns..

Use the space as the separator and pick a convenient destination column.
Tell the Wizard to only import the field with the "4" in it


--
Gary''s Student - gsnu200750


"Jambruins" wrote:

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.

that would work but I am using a web query to import the data and I update it
everyday. Instead of having to do the sort thing everyday I would like a
formua. Any ideas? Thanks.

"Gary''s Student" wrote:

Select the cells in column B and pull-down:

Data Text to Columns..

Use the space as the separator and pick a convenient destination column.
Tell the Wizard to only import the field with the "4" in it


--
Gary''s Student - gsnu200750


"Jambruins" wrote:

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.

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.

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.

Have you considered recording a macro whilst going through the Text Wizard steps
as outlined?

Just assign to button and run each day when you refresh.


Gord Dibben MS Excel MVP

On Mon, 22 Oct 2007 10:20:02 -0700, Jambruins
wrote:

that would work but I am using a web query to import the data and I update it
everyday. Instead of having to do the sort thing everyday I would like a
formua. Any ideas? Thanks.

"Gary''s Student" wrote:

Select the cells in column B and pull-down:

Data Text to Columns..

Use the space as the separator and pick a convenient destination column.
Tell the Wizard to only import the field with the "4" in it


--
Gary''s Student - gsnu200750


"Jambruins" wrote:

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.

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.

I am not very familiar with macros. Can you explain how I would do this and
what it would do for me? Thanks.

"Gord Dibben" wrote:

Have you considered recording a macro whilst going through the Text Wizard steps
as outlined?

Just assign to button and run each day when you refresh.


Gord Dibben MS Excel MVP

On Mon, 22 Oct 2007 10:20:02 -0700, Jambruins
wrote:

that would work but I am using a web query to import the data and I update it
everyday. Instead of having to do the sort thing everyday I would like a
formua. Any ideas? Thanks.

"Gary''s Student" wrote:

Select the cells in column B and pull-down:

Data Text to Columns..

Use the space as the separator and pick a convenient destination column.
Tell the Wizard to only import the field with the "4" in it


--
Gary''s Student - gsnu200750


"Jambruins" wrote:

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.

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.

Did you see my 2nd posting in this thread... it has the modified formula you
want.

Rick

works perfectly, thanks

"Rick Rothstein (MVP - VB)" wrote:

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.

Did you see my 2nd posting in this thread... it has the modified formula you
want.

Rick

I recorded this macro while doing the Text to Columns Wizard steps Gary's
Student outlined.

Selected A1:A10 with your sample data entered.

Sub Macro1()
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 9), Array(3, 9), Array(4, 9), _
Array(5, 1), Array(6, 9)), TrailingMinusNumbers:=True
End Sub

The number appeared in B1:B10

Works with or without the (SO)


Gord

On Mon, 22 Oct 2007 10:57:03 -0700, Jambruins
wrote:

I am not very familiar with macros. Can you explain how I would do this and
what it would do for me? Thanks.

"Gord Dibben" wrote:

Have you considered recording a macro whilst going through the Text Wizard steps
as outlined?

Just assign to button and run each day when you refresh.


Gord Dibben MS Excel MVP

On Mon, 22 Oct 2007 10:20:02 -0700, Jambruins
wrote:

that would work but I am using a web query to import the data and I update it
everyday. Instead of having to do the sort thing everyday I would like a
formua. Any ideas? Thanks.

"Gary''s Student" wrote:

Select the cells in column B and pull-down:

Data Text to Columns..

Use the space as the separator and pick a convenient destination column.
Tell the Wizard to only import the field with the "4" in it


--
Gary''s Student - gsnu200750


"Jambruins" wrote:

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.

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.

Well, no more elegant than the other submissions, but works for either
case via IF:

=IF(ISERROR(FIND("(SO)",A1)),--RIGHT(A1,LEN(A1)-
FIND(CHAR(200),SUBSTITUTE(A1," ",CHAR(200),LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))),--
RIGHT(TRIM(LEFT(A1,FIND("(SO)",A1)-1)),LEN(TRIM(LEFT(A1,FIND("(SO)",A1)-1)))-
FIND(CHAR(200),SUBSTITUTE(TRIM(LEFT(A1,FIND("(SO)" ,A1)-1)),"
",CHAR(200),LEN(TRIM(LEFT(A1,FIND("(SO)",A1)-1)))-
LEN(SUBSTITUTE(TRIM(LEFT(A1,FIND("(SO)",A1)-1))," ",""))))))


* works for no (SO)
* works regardless of whether there's a space before (SO) or not
* DOES NOT work if (SO) is not fully enclosed in ()


On Oct 22, 1:01 pm, Jambruins
wrote:
Not all of the cells have the (SO) at the end either. Some of them end like
this
Carolina 3 at Pittsburgh 4



"Jambruins" wrote:
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.- Hide quoted text -

- Show quoted text -

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.

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.

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.

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.

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.

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

Well, not exactly... if the character is a "p", you will have the same
problem (for the same reason). Here is a patch to your formula to fix this
problem...

=LOOKUP(1000,--MID(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"a","z"),"p"," z"),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"))))

Yes, your formula is now longer, but it still is around half the size of the
one I posted.

Rick

Yeah, that'll work. I didn't have time to dig any deeper and look for a
tweak.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
So, as long as the next character after the last number that
is <12 < "a" it will work!

Well, not exactly... if the character is a "p", you will have the same
problem (for the same reason). Here is a patch to your formula to fix this
problem...

=LOOKUP(1000,--MID(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"a","z"),"p"," z"),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"))))

Yes, your formula is now longer, but it still is around half the size of
the one I posted.

Rick

I'm not 100% sure why this works, but it does seem to work. This formula
removes one of the SUBSTITUTE function calls and arrays the "find me" text
(saving some 15 characters in the process)... and it seems to always return
the correct answer too:

=LOOKUP(1000,--MID(SUBSTITUTE(LOWER(A1),{"a","p"},"z"),MIN(FIND({ 0,1,2,3,4,5,6,7,8,9},A1&{0,1,2,3,4,5,6,7,8,9},SEAR CH("
at ",A1))),ROW(INDIRECT("1:255"))))

Rick


"T. Valko" wrote in message
...
Yeah, that'll work. I didn't have time to dig any deeper and look for a
tweak.

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
So, as long as the next character after the last number that
is <12 < "a" it will work!

Well, not exactly... if the character is a "p", you will have the same
problem (for the same reason). Here is a patch to your formula to fix
this problem...

=LOOKUP(1000,--MID(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"a","z"),"p"," z"),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"))))

Yes, your formula is now longer, but it still is around half the size of
the one I posted.

Rick