Another text to columns

Davebro

Board Regular
Joined
Feb 22, 2018
Messages
150
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
Is it possible to split the teams into two columns.

Leicester City BrentfordLeicester CityBrentford
Everton Manchester United
Bournemouth Wolverhampton
Arsenal West Ham
Fulham Crystal Palace
Ipswich Town Tottenham
Southampton Brighton
Aston Villa Chelsea
Newcastle United Nottingham Forest
 
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.
 
Upvote 0
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
ABCDE
1Leicester City BrentfordLeicester CityBrentfordArsenal
2Everton Manchester UnitedEvertonManchester UnitedAston Villa
3Bournemouth WolverhamptonBournemouthWolverhamptonBournemouth
4Arsenal West HamArsenalWest HamBrentford
5Fulham Crystal PalaceFulhamCrystal PalaceBrighton
6Ipswich Town TottenhamIpswich TownTottenhamChelsea
7Southampton BrightonSouthamptonBrightonCrystal Palace
8Aston Villa ChelseaAston VillaChelseaEverton
9Newcastle United Nottingham ForestNewcastle UnitedNottingham ForestFulham
10Ipswich Town
11Leicester City
12Manchester United
13Newcastle United
14Nottingham Forest
15Southampton
16Tottenham
17West ham
18Wolverhampton
Teams
Cell Formulas
RangeFormula
B1:B9B1=LOOKUP(1,FIND(E$1:E$18,A1),E$1:E$18)
C1:C9C1=TRIM(SUBSTITUTE(A1,B1,""))
 
Upvote 0
Solution
Many thanks for this, I suspected that would be the route, thanks so much for the formulas.
 
Upvote 0

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