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

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

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

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

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

If you use the text to column, you could then use =right(A1,2)

What if A1 = New England 3 ?

Biff

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