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.
 
I assume that I am doing something wrong, but now it does not seem to do anything. It seems to run the code, but does not result in any change to the actual data.

I think this modified code fixes that problem and another potential problem that I think could also affect the results.
Code:
[TABLE="width: 500"]
<tbody>[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]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Here is another way by using a User defined function. Add the code and then apply the formula as shown below

Code:
Function SPLIT_TEAMS(ByVal S As String, NTh As Long) As Variant
Dim j As Long

For j = 1 To Len(S)
    If Mid(S, j, 3) Like "#-#" Then Exit For
Next j

Mid(S, InStrRev(S, " ", j, vbBinaryCompare), 1) = Chr(169)
Mid(S, InStr(j, S, " ", vbBinaryCompare), 1) = Chr(169)
Mid(S, j + 1, 1) = Chr(169)

SPLIT_TEAMS = Split(S, Chr(169), NTh + 1, vbBinaryCompare)(NTh - 1)
End Function



<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=SPLIT_TEAMS(<font color="Blue">$A2,COLUMNS(<font color="Red">$B2:B2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />




Excel 2010
ABCDE
1ResultTeam 1Score Team 1Score Team 2Team 2
2West Haven 90-100 Spencer-GreenWest Haven90100Spencer-Green
Sheet24
 
Upvote 0
I assume that I am doing something wrong, but now it does not seem to do anything. It seems to run the code, but does not result in any change to the actual data.
It works for me with the dummy data I made up to test it... and it also seems to work for Dryver14 (Message #10 ) as well. It is possible that your actual data may be the problem (maybe your spaces are not "real" spaces and maybe your dashes are not ASCII dashes). Can you post a copy of your workbook to DropBox so that we can download it here and try our solutions out on your actual data instead of the data we make up?



Here is another way by using a User defined function. Add the code and then apply the formula as shown below
I had thought about doing a UDF at first, but then thought that will over 50,000 rows of existing data, and the need for 4 formulas per row, that loading up the worksheet with over the 200,000 plus UDF formulas seemed like it might be somewhat burdensome.
 
Last edited:
Upvote 0
I had thought about doing a UDF at first, but then thought that will over 50,000 rows of existing data, and the need for 4 formulas per row, that loading up the worksheet with over the 200,000 plus UDF formulas seemed like it might be somewhat burdensome.[/QUOTE]

the UDF above will be indeed slower. Perhaps making a UDF that doesnt have to split the string each time:

Code:
Function SPLIT_TEAMS(ByVal S As String) As Variant
Dim j As Long

For j = 1 To Len(S)
    If Mid(S, j, 3) Like "#-#" Then Exit For
Next j

Mid(S, InStrRev(S, " ", j, vbBinaryCompare), 1) = Chr(169)
Mid(S, InStr(j, S, " ", vbBinaryCompare), 1) = Chr(169)
Mid(S, j + 1, 1) = Chr(169)
SPLIT_TEAMS = Split(S, Chr(169), , vbBinaryCompare)
End Function

and then select 4 adjacent cells and entering it as an array in 4 cells at the same time will be faster, though still not beat the macro


Excel 2010
BCDE
2West Haven90100Spencer-Green
Sheet24
Cell Formulas
RangeFormula
B2:E2{=SPLIT_TEAMS(A2)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Rick, I know that you are right about the hyphens and I . There is alot of screwy stuff with the data in it's current state because as I mentioned originally, it was OCR'ed from a book, additionally, the book had them all listed more or less in a sort of paragraph type format. So, I have done as much cleanup as I could on a large scale (find/replace, etc.) in a Word document and then pasted into Excel where there is still a fair amount of cleanup. I have had alot of luck with other projects posting on here, so I figured I would give that a shot....and here we are. So, sure, I can send what I have at the moment. Where would I send it?


It works for me with the dummy data I made up to test it... and it also seems to work for Dryver14 (Message #10 ) as well. It is possible that your actual data may be the problem (maybe your spaces are not "real" spaces and maybe your dashes are not ASCII dashes). Can you post a copy of your workbook to DropBox so that we can download it here and try our solutions out on your actual data instead of the data we make up?




I had thought about doing a UDF at first, but then thought that will over 50,000 rows of existing data, and the need for 4 formulas per row, that loading up the worksheet with over the 200,000 plus UDF formulas seemed like it might be somewhat burdensome.
 
Upvote 0
Rick, I know that you are right about the hyphens and I . There is alot of screwy stuff with the data in it's current state because as I mentioned originally, it was OCR'ed from a book, additionally, the book had them all listed more or less in a sort of paragraph type format. So, I have done as much cleanup as I could on a large scale (find/replace, etc.) in a Word document and then pasted into Excel where there is still a fair amount of cleanup. I have had alot of luck with other projects posting on here, so I figured I would give that a shot....and here we are. So, sure, I can send what I have at the moment. Where would I send it?
If you don't have one, open a DropBox account and upload the file there, mark the file for sharing and then post the link for that file here... that way, anyone who is interested in trying to help with your question can download the file and work on it independently.
 
Upvote 0
Upvote 0
I think the issue is simple, the code is running on column A the words to split are on column E
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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