find a number in a cell

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Gord Dibben]

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

 

[Guest]

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.

 

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

works perfectly, thanks

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

Rick

 

[Gord Dibben]

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

 

[T. Valko]

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))),Length))

 

[iliace]

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 ()

 

[Rick Rothstein \(MVP - VB\)]

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,
<space>at<space>) on the off chance that some of the other words in the text
have "at" embedded within them.

Rick

 

[T. Valko]

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

for "_at_"

Yep, good point!

 

[Rick Rothstein \(MVP - VB\)]

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]

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!

 

[T. Valko]

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!

 

[Rick Rothstein \(MVP - VB\)]

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