Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
up vote 2 down vote accepted

To get the first team:


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()
    GetFirstTeam = Trim(Split(ActiveCell.Offset(0, -1), "(")(0))
End Function

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

Then in B1:


And in C1:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .