Formulas for separating cell contents in to four separate cells

knpaddac

New Member
Joined
Feb 11, 2014
Messages
33
I have 1 column with over 50,000 rows containing data strings (they are actually basketball game scores) that are currently formatted like this:

Teama ##-## Teamb

The '#' represent numeric digits, although many were interpreted as letters or other symbols by OCR software. These start in cell E2. I am wanting to use formulas to separate that data from E2 out into the four cells to the right looking something like this:
F2-- Teama
G2-- ## (the first score)
H2-- ## (second score)
I2-- Teamb

There is some inconsistency in that sometimes the ## is actually a single digit or even three, and there are also names that have spaces or hyphens in the middle (West Haven or Spencer-Green). Basically I am looking for any help to create formulas that will do the bulk of the work and then I assume that I will have to go back and do some manipulation of those that do not fit exactly.

Any help is appreciated.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Have you looked at Text to columns on the data tab? This might achieve exactly what you are after
 
Upvote 0
Those inconsistencies are what will make a formula solution problematice, but you can do what you want using this macro...
Code:
[table="width: 500"]
[tr]
	[td]Sub SplitTeamsAndScores()
  Dim R As Long, X As Long, Txt As String, Data As Variant, Answer As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Answer(1 To UBound(Data), 1 To 1)
  For R = 1 To UBound(Data)
    Txt = Data(R, 1)
    For X = 1 To Len(Txt)
      If Mid(Txt, X, 3) Like "#-#" Then
        Txt = Left(Txt, X) & Chr(1) & Mid(Txt, X + 2)
        Txt = Application.Replace(Txt, InStrRev(Txt, " ", X), 1, Chr(1))
        Txt = Application.Replace(Txt, InStr(X, Txt, " "), 1, Chr(1))
        Answer(R, 1) = Txt
      End If
    Next
  Next
  Range("B1").Resize(UBound(Answer)) = Answer
  Columns("B").TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (SplitTeamsAndScores) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:
Upvote 0
I have 1 column with over 50,000 rows containing data strings (they are actually basketball game scores) that are currently formatted like this:

Teama ##-## Teamb

The '#' represent numeric digits, although many were interpreted as letters or other symbols by OCR software. These start in cell E2. I am wanting to use formulas to separate that data from E2 out into the four cells to the right looking something like this:
F2-- Teama
G2-- ## (the first score)
H2-- ## (second score)
I2-- Teamb

There is some inconsistency in that sometimes the ## is actually a single digit or even three, and there are also names that have spaces or hyphens in the middle (West Haven or Spencer-Green). Basically I am looking for any help to create formulas that will do the bulk of the work and then I assume that I will have to go back and do some manipulation of those that do not fit exactly.

Any help is appreciated.


Messy, but works :)


Excel 2010
ABCDE
1Original Col1Col2Col3Col4
2EastVille-Landrunners 71 -23 Krypton-Space EastVille-Landrunners7123Krypton-Space
3Neutral-Brainers 8 -129 Prof-Baskets Neutral-Brainers8129Prof-Baskets
4Size 71 -78 Penetrate Size7178Penetrate
5Prof-Baskets 124 -119 Smallville Prof-Baskets124119Smallville
6Miner 128 -12 Rhetoric-Question Miner12812Rhetoric-Question
7Addicted 37 -27 Size Addicted3727Size
8Size 127 -123 Complain Size127123Complain

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
B2=TRIM(LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1))
C2=TRIM(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),FIND("-",MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),10))-1))
D2=TRIM(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+FIND("-",MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),10)),(MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+FIND("-",MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),10))-1,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+FIND("-",MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),10))-1)&"0123456789")))+1))
E2=TRIM(RIGHT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+FIND("-",MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),10))-1,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+FIND("-",MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),10))-1),LEN(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+FIND("-",MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),10))-1,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+FIND("-",MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),10))-1))-FIND(" ",MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+FIND("-",MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),10))-1,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+FIND("-",MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),10))-1))))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Rick I had a go with your code

You get the above error if you only have one item to parse.

I have tried several times with plenty of lines and the code stops here

Columns("B").TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)

Run time error 1004 No data was selected to parse
 
Upvote 0
Rick I had a go with your code

You get the above error if you only have one item to parse.
ReDim Answer(1 To UBound(Data), 1 To 1)
I think this modified code fixes that problem and another potential problem that I think could also affect the results.
Code:
[table="width: 500"]
[tr]
	[td]Sub SplitTeamsAndScores()
  Dim R As Long, X As Long, Txt As String, Data As Variant, Answer As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp).Offset(1))
  ReDim Answer(1 To UBound(Data), 1 To 1)
  For R = 1 To UBound(Data)
    Txt = Replace(Replace(Data(R, 1), " -", "-"), "- ", "-")
    For X = 1 To Len(Txt)
      If Mid(Txt, X, 3) Like "#-#" Then
        Txt = Left(Txt, X) & Chr(1) & Mid(Txt, X + 2)
        Txt = Application.Replace(Txt, InStrRev(Txt, " ", X), 1, Chr(1))
        Txt = Application.Replace(Txt, InStr(X, Txt, " "), 1, Chr(1))
        Answer(R, 1) = Txt
      End If
    Next
  Next
  Range("B1").Resize(UBound(Answer)) = Answer
  Columns("B").TextToColumns , xlDelimited, , , False, False, False, False, True, Chr(1)
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top