Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 - |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#11
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#12
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#13
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#14
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#15
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#16
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#17
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#18
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
formula to find how many cell have a number between 2 numbers. | Excel Worksheet Functions | |||
Is there a way to find out whether theres a number in a cell?? | Excel Discussion (Misc queries) | |||
Can I find the number of letters in a cell? | Excel Discussion (Misc queries) | |||
Find the 1st occurance of a number in a cell | Excel Worksheet Functions | |||
find the cell above any number in any range | Excel Worksheet Functions |