If you can get rid of the complicating factor of spaces within the team name,
i.e. change New England to New_England or NewEngland? If so, then you could
use Data/Text to columns to split using either space or comma as the
delimiters. If New England is the only "problem name" you could use
search/replace to change it, do the separation, then change it back.

If you want a VBA function, the one below should do.

You need to paste the code below into a standard module in your workbook, then
with A1 containing the text

New England 30, Oakland 20

you would select 4 adjacent cells in a row, say B1:E1, and in B1 type this
array formula:

=ParseScore(A1)

then press CTRL+SHIFT+ENTER to enter it.


Function ParseScore(sText As String) As Variant
Dim i As Long
Dim j As Long
Dim Result(0 To 3) As Variant
Dim Parts As Variant

If InStr(sText, ",") = 0 Then
ParseScores = Array("No comma")
Exit Function
End If

Parts = Split(Replace(Trim$(sText), " ", " "), ",")
For i = 0 To 1
j = InStrRev(Parts(i), " ")
Result(i * 2) = Trim$(Left$(Parts(i), j - 1))
Result(i * 2 + 1) = Val(Trim$(Mid$(Parts(i), j + 1)))
Next i
ParseScore = Result()
End Function

On Wed, 5 Oct 2005 11:57:07 -0700, Jambruins
wrote:

that sort of works. I would like the New England to be by itself also
though.

"JR" wrote:

If you use the text to column, you could then use =right(A1,2) to get
A1 B1
New England 30 30

"Jambruins" wrote:

Cell A1 = New England 30, Oakland 20.

Is it possible to have the following:
cell B1 = New England
cell C1 = 30
cell D1 = Oakland
cell E1 = 20

Cells A2-A16 are similar but the team name and number changes and the
team
names are all different lengths.

If that won't work I could use text to columns and split it at the , so
cell
A1 = New England 30 and cell B1 = Oakland 20. But I would still want the
team name and score to be separated. Using the above method would save
me
from having to use the text to columns command.

Thanks