Excel formula for extracting text

Question

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.

Answer 1

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)

Answer 2

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