Split text and number

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Myrna Larson]

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

 

[Biff]

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:"&LEN(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

 

[Biff]

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

What if A1 = New England 3 ?

Biff