Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have the following within cell A1:

NEW ORLEANS (0-0) at GREEN BAY (0-0)

I would like a formula to place in cell B1 "NEW ORLEANS" and in C1 "GREEN BAY.

share|improve this question
    
Does the text change or is it always as you gave? –  Linker3000 Sep 8 '11 at 14:34
    
@Linker3000: The text will change. Thanks –  Mike Sep 8 '11 at 14:35
    
Ok, so can we say you want the text before the first ( in B1 and the text between 'at' and second ( in C1 ?? –  Linker3000 Sep 8 '11 at 14:44
    
@Linker3000: Yes, that's it. –  Mike Sep 8 '11 at 14:48

2 Answers 2

up vote 2 down vote accepted

To get the first team:

=LEFT(A1,FIND("(",A1)-2)

To get the second team:

=MID(A1,FIND(" at ",A1)+4,FIND("(",A1,FIND(")",A1))-FIND(" at ",A1)-5)
share|improve this answer
    
Worked Great no matter what the score stated is. Thanks !! –  Mike Sep 8 '11 at 15:46

So if you had that cell in A1, then this in B1:

=LEFT(A1, FIND("(", A1)-2)

And this in C1:

=MID(A1, FIND(")", A1) + 4, LEN(A1)-FIND(")", A1)-9)

Note that this is very dependent on the sample that you gave being representative of the layout of the text in the other cells. It is based on finding the Parenthesis in the string. Actually I just realized that it will probably break if the numerals in the second (0-0) go to double digits. To account for that you would have to look for the second set of parens to determine the length for the Mid() call. To be honest, if this were my problem to solve, then I think that I would just write a VBA macro using split.

So via VBA, I would do something like this. Add these two functions:

Function GetFirstTeam()
    Application.Volatile
    GetFirstTeam = Trim(Split(ActiveCell.Offset(0, -1), "(")(0))
End Function

Function GetSecondTeam()
    Application.Volatile
    GetSecondTeam = Trim(Split(Split(ActiveCell.Offset(0, -2), " at ")(1), "(")(0))
End Function

Then in B1:

=GetFirstTeam()

And in C1:

=GetSecondTeam()
share|improve this answer
    
Thanks for the help. You are correct by stating the numbers might be a double digit. I was really hoping to stay away from VBA. Is there anyway to do that or is it getting too complicated? –  Mike Sep 8 '11 at 14:50
1  
if you change C1 to this, it works for double-digit as well as single- and multi-word teams (until the 2nd team goes 10-10, anyway): =MID(A1, FIND("at", A1) + 2, LEN(A1)-FIND(")", A1)-9) –  StevenV Sep 8 '11 at 15:21
    
@StevenV : Thanks, it worked great. It looks like there's a space before the second team in C1. Is there a way to remove it with the formula you stated? =MID(A1, FIND("at", A1) + 2, LEN(A1)-FIND(")", A1)-9) –  Mike Sep 8 '11 at 15:33
1  
If you are going to do it via formula and acomodate any score then you would just need to find the location of "at" for the start of the mid, Then find the location of the second "(" and the difference of those two values minus 4 would be the length of the mid. I'm just too lazy to put that into a formula. –  EBGreen Sep 8 '11 at 15:36
1  
getting closer, though I still have some scenarios where it fails: =LEFT(MID(A1, FIND("at", A1) + 3, LEN(A1)-FIND(")", A1)), FIND("(", A1)-3) –  StevenV Sep 8 '11 at 15:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.