Another text to columns

[Davebro]

Is it possible to split the teams into two columns.

Leicester City Brentford	Leicester City	Brentford
Everton Manchester United
Bournemouth Wolverhampton
Arsenal West Ham
Fulham Crystal Palace
Ipswich Town Tottenham
Southampton Brighton
Aston Villa Chelsea
Newcastle United Nottingham Forest

 

[Joe4]

Can you clearly define a rule for how the entries with 3 and 4 words should be split?
If you cannot clearly define a rule in plain English, I don't know you can expect to program something that cannot be defined.

 

[Peter_SSs]

Since there is no logical way to tell which column might have multiple words, you would need a list of team names. Then you could use something like this.

25 02 21.xlsm
	A	B	C	D	E
1	Leicester City Brentford	Leicester City	Brentford		Arsenal
2	Everton Manchester United	Everton	Manchester United		Aston Villa
3	Bournemouth Wolverhampton	Bournemouth	Wolverhampton		Bournemouth
4	Arsenal West Ham	Arsenal	West Ham		Brentford
5	Fulham Crystal Palace	Fulham	Crystal Palace		Brighton
6	Ipswich Town Tottenham	Ipswich Town	Tottenham		Chelsea
7	Southampton Brighton	Southampton	Brighton		Crystal Palace
8	Aston Villa Chelsea	Aston Villa	Chelsea		Everton
9	Newcastle United Nottingham Forest	Newcastle United	Nottingham Forest		Fulham
10					Ipswich Town
11					Leicester City
12					Manchester United
13					Newcastle United
14					Nottingham Forest
15					Southampton
16					Tottenham
17					West ham
18					Wolverhampton
Teams

Cell Formulas
Range	Formula
B1:B9	B1	=LOOKUP(1,FIND(E$1:E$18,A1),E$1:E$18)
C1:C9	C1	=TRIM(SUBSTITUTE(A1,B1,""))

 

[Davebro]

Many thanks for this, I suspected that would be the route, thanks so much for the formulas.

 

[Peter_SSs]

You're welcome.