Split data in cells

[Davebro]

First column to be separated if possible

Champions LeagueBayern MunichCeltic3:1	Champions League	Bayern	Celtic	3:1
Champions LeagueAtalantaClub Brugge2:1	Champions League	AtalantaClub	Brugge	2:1
England - League OneWycombeBristol Rovers2:0	England League One	Wycombe	Bristol Rovers	2:0
England - ChampionshipPreston North EndMillwall2:1	England Championship	Preston North End	Millwall	2:1

 

[jacobrcotton]

Because competitions and teams can be...basically anything, and even the nation prefix (England, Spain, Germany, etc) is fairly fixed but could be a long list, you really need a delimiter between the sections of data.

If that isn't possible, you'll really need a hefty list to tell the workbook what to look for. Something like

=left([cell], 5)
Then do a quick match/lookup to see if those first 5 characters are "Champ" or "Europ" or "Engla" or "Germa", and from there, you could code to (1) deduce what that first intro value is and how long that entry should be, then you determine when the next entry will start.

However, you quickly get into the issue of identifying the clubs themselves. If you're talking about just the first 4 tiers of the Big Five (England, Spain, Germany, France, Italy), that would be some 450ish teams. But because we have Champions League, you'd also need to include the entire First Tier of basically every club in UEFA, and probably the 2nd tier & 3rd tier if you'd like it to work for several years. That list would get VERY long, indeed.

Is there another source of the match results that does include delimiters?

 

[Peter_SSs]

Do you have the new REGEXEXTRACT function in your 365 version?
If so, you could see if this is any use.
(BTW, shouldn't it be "Club Brugge" as below rather than "AtlantaClub" as in your sample?)

Davebro.xlsm
	A	B	C	D	E
1	Champions LeagueBayern MunichCeltic3:1	Champions League	Bayern Munich	Celtic	3:1
2	Champions LeagueAtalantaClub Brugge2:1	Champions League	Atalanta	Club Brugge	2:1
3	England - League OneWycombeBristol Rovers2:0	England - League One	Wycombe	Bristol Rovers	2:0
4	England - ChampionshipPreston North EndMillwall2:1	England - Championship	Preston North End	Millwall	2:1
Split Text

Cell Formulas
Range	Formula
B1:B4	B1	=REGEXEXTRACT(A1,".+?[a-z](?=[A-Z])")
C1:C4	C1	=SUBSTITUTE(REGEXEXTRACT(A1,".+[a-z](?=[A-Z])"),B1,"")
D1:D4	D1	=SUBSTITUTE(REGEXEXTRACT(A1,".+?(?=\d)"),B1&C1,"")
E1:E4	E1	=REGEXEXTRACT(A1,"[0-9:]+$")

 

[Peter_SSs]

Do you have the new REGEXEXTRACT function in your 365 version?

If not, or you want/need to use other Excel versions, you could see if this user-defined function (UDF) does what you need. To implement ..

1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in B1 in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

N.B. With this UDF and the formula suggestion above, your actual data may have more variations which might mean tweaks to either formulas or code (or it may prove that these approaches are not robust enough for your data)

Function SplitIt(s As String) As Variant
  Dim RX As Object, M As Object
  Dim a(1 To 4)
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "[A-Z].+?\w(?=[A-Z]|\d)"
  Set M = RX.Execute(s)
  a(1) = M.Item(0)
  a(2) = M.Item(1)
  a(3) = M.Item(2)
  a(4) = Mid(s, Len(a(1) & a(2) & a(3)) + 1)
  SplitIt = a
End Function

Davebro.xlsm
	A	B	C	D	E
1	Champions LeagueBayern MunichCeltic3:1	Champions League	Bayern Munich	Celtic	3:1
2	Champions LeagueAtalantaClub Brugge2:1	Champions League	Atalanta	Club Brugge	2:1
3	England - League OneWycombeBristol Rovers2:0	England - League One	Wycombe	Bristol Rovers	2:0
4	England - ChampionshipPreston North EndMillwall2:1	England - Championship	Preston North End	Millwall	2:1
Split Text (2)

Cell Formulas
Range	Formula
B1:E4	B1	=SplitIt(A1)
Dynamic array formulas.

 

[Davebro]

WOW, thank you so much for this, it has save me a lot of time splitting these up. Much appreciated.

 

[Peter_SSs]

You're welcome. Glad it helped. Thanks for the follow-up.