Hi!

B1 = formula entered as an array using the key combo of CTRL,SHIFT,ENTER:

=LEFT(A1,MATCH(1,(CODE(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1))47)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN (A1))),1))<58),0)-2)

C1 = formula (normally entered):

=MID(A1,LEN(B1)+2,FIND(",",A1)-LEN(B1)-2)*1

D1 = formula (normally entered):

=MID(A1,FIND(",",A1)+2,FIND("~",SUBSTITUTE(A1,"
","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-(FIND(",",A1)+2))

E1 = formula (normally entered):

=SUBSTITUTE(A1,B1&" "&C1&", "&D1&" ","")*1

Select B1:E1 then copy down as needed.

Note: no error checking in any of those formulas. As long as the format of
your data is consistent with the sample you posted there should be no
problem.

Biff

"Jambruins" wrote in message
...
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