seperating text to columns without spacing or punctuation

jubby059

New Member
Joined
Jul 7, 2014
Messages
5
I want to separate the team names from the first table to a new column using a formula.

[TABLE="width: 260"]
<tbody>[TR]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Adam LallanaSouthampton[/TD]
[/TR]
[TR]
[TD]Ahmed ElmohamadyHull[/TD]
[/TR]
[TR]
[TD]Brad GuzanAston Villa[/TD]
[/TR]
[TR]
[TD]John RuddyNorwich[/TD]
[/TR]
[TR]
[TD]Leon OsmanEverton[/TD]
[/TR]
[TR]
[TD]Mark NobleWest Ham[/TD]
[/TR]
[TR]
[TD]Mile JedinakCrystal Palace[/TD]
[/TR]
[TR]
[TD]Simon MignoletLiverpool[/TD]
[/TR]
[TR]
[TD]Steve SidwellFulham[/TD]
[/TR]
[TR]
[TD]Steven CaulkerCardiff[/TD]
[/TR]
</tbody>[/TABLE]

I want the result to be like this

[TABLE="width: 260"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Team Name[/TD]
[/TR]
[TR]
[TD]Adam Lallana[/TD]
[TD]Southampton[/TD]
[/TR]
[TR]
[TD]Ahmed Elmohamady[/TD]
[TD]Hull[/TD]
[/TR]
[TR]
[TD]Brad Guzan[/TD]
[TD]Aston Villa[/TD]
[/TR]
[TR]
[TD]John Ruddy[/TD]
[TD]Norwich[/TD]
[/TR]
[TR]
[TD]Leon Osman[/TD]
[TD]Everton[/TD]
[/TR]
[TR]
[TD]Mark Noble[/TD]
[TD]West Ham[/TD]
[/TR]
[TR]
[TD]Mile Jedinak[/TD]
[TD]Crystal Palace[/TD]
[/TR]
[TR]
[TD]Simon Mignolet[/TD]
[TD]Liverpool[/TD]
[/TR]
[TR]
[TD]Steve Sidwell[/TD]
[TD]Fulham[/TD]
[/TR]
[TR]
[TD]Steven Caulker[/TD]
[TD]Cardiff[/TD]
[/TR]
</tbody>[/TABLE]

I also have a list of teams that I think might be useful

[TABLE="width: 113"]
<tbody>[TR]
[TD]AaB[/TD]
[/TR]
[TR]
[TD]Académica[/TD]
[/TR]
[TR]
[TD]Ajax[/TD]
[/TR]
[TR]
[TD]Anderlecht[/TD]
[/TR]
[TR]
[TD]Anzhi[/TD]
[/TR]
[TR]
[TD]Atlético[/TD]
[/TR]
[TR]
[TD]AZ[/TD]
[/TR]
[TR]
[TD]Barcelona[/TD]
[/TR]
[TR]
[TD]Bari 1908[/TD]
[/TR]
[TR]
[TD]Barnsley[/TD]
[/TR]
[TR]
[TD]Bayern München[/TD]
[/TR]
[TR]
[TD]Benfica[/TD]
[/TR]
[TR]
[TD]Beşiktaş[/TD]
[/TR]
[TR]
[TD]Betis[/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[/TR]
[TR]
[TD]Blackburn[/TD]
[/TR]
[TR]
[TD]Boca[/TD]
[/TR]
[TR]
[TD]Bologna[/TD]
[/TR]
[TR]
[TD]Bolton[/TD]
[/TR]
[TR]
[TD]Bordeaux[/TD]
[/TR]
[TR]
[TD]Botafogo[/TD]
[/TR]
[TR]
[TD]Braga[/TD]
[/TR]
[TR]
[TD]Braunschweig[/TD]
[/TR]
[TR]
[TD]Bremen[/TD]
[/TR]
[TR]
[TD]Brescia[/TD]
[/TR]
[TR]
[TD]Brighton[/TD]
[/TR]
[TR]
[TD]Bristol Rovers[/TD]
[/TR]
[TR]
[TD]Bursa[/TD]
[/TR]
[TR]
[TD]Cali[/TD]
[/TR]
[TR]
[TD]Cardiff[/TD]
[/TR]
[TR]
[TD]Celta de Vigo[/TD]
[/TR]
[TR]
[TD]Celtic[/TD]
[/TR]
[TR]
[TD]Chievo[/TD]
[/TR]
[TR]
[TD]Cittadella[/TD]
[/TR]
[TR]
[TD]Clermont[/TD]
[/TR]
[TR]
[TD]Club Brugge[/TD]
[/TR]
[TR]
[TD]Corinthians[/TD]
[/TR]
[TR]
[TD]Crvena Zvezda[/TD]
[/TR]
[TR]
[TD]Crystal Palace[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[/TR]
[TR]
[TD]Danubio[/TD]
[/TR]
[TR]
[TD]Defensor[/TD]
[/TR]
[TR]
[TD]Dinamo[/TD]
[/TR]
[TR]
[TD]Dinamo Zagreb[/TD]
[/TR]
[TR]
[TD]Düsseldorf[/TD]
[/TR]
[TR]
[TD]Elche[/TD]
[/TR]
[TR]
[TD]Espanyol[/TD]
[/TR]
[TR]
[TD]Estudiantes[/TD]
[/TR]
[TR]
[TD]Evian TG[/TD]
[/TR]
[TR]
[TD]Fenerbahçe[/TD]
[/TR]
[TR]
[TD]Feyenoord[/TD]
[/TR]
[TR]
[TD]Fiorentina[/TD]
[/TR]
[TR]
[TD]Fluminense[/TD]
[/TR]
[TR]
[TD]Fulham[/TD]
[/TR]
[TR]
[TD]Galatasaray[/TD]
[/TR]
[TR]
[TD]Genoa[/TD]
[/TR]
[TR]
[TD]Górnik[/TD]
[/TR]
[TR]
[TD]Granada[/TD]
[/TR]
[TR]
[TD]Groningen[/TD]
[/TR]
[TR]
[TD]Grosseto[/TD]
[/TR]
[TR]
[TD]Hajduk[/TD]
[/TR]
[TR]
[TD]Hamburg[/TD]
[/TR]
[TR]
[TD]Hoffenheim[/TD]
[/TR]
[TR]
[TD]Huddersfield[/TD]
[/TR]
[TR]
[TD]IFK Göteborg[/TD]
[/TR]
[TR]
[TD]Inter[/TD]
[/TR]
[TR]
[TD]Jazira[/TD]
[/TR]
[TR]
[TD]Juve Stabia[/TD]
[/TR]
[TR]
[TD]Juventus[/TD]
[/TR]
[TR]
[TD]Kalmar[/TD]
[/TR]
[TR]
[TD]Karpaty[/TD]
[/TR]
[TR]
[TD]Krasnodar[/TD]
[/TR]
[TR]
[TD]Las Palmas[/TD]
[/TR]
[TR]
[TD]Lazio[/TD]
[/TR]
[TR]
[TD]Le Mans[/TD]
[/TR]
[TR]
[TD]Lech[/TD]
[/TR]
[TR]
[TD]Legia[/TD]
[/TR]
[TR]
[TD]Lens[/TD]
[/TR]
[TR]
[TD]Leverkusen[/TD]
[/TR]
[TR]
[TD]Lille[/TD]
[/TR]
[TR]
[TD]Liverpool[/TD]
[/TR]
[TR]
[TD]Lyon[/TD]
[/TR]
[TR]
[TD]Maccabi TA[/TD]
[/TR]
[TR]
[TD]Málaga[/TD]
[/TR]
[TR]
[TD]Mallorca[/TD]
[/TR]
[TR]
[TD]Man Utd[/TD]
[/TR]
[TR]
[TD]Marseille[/TD]
[/TR]
[TR]
[TD]Milan[/TD]
[/TR]
[TR]
[TD]Molde[/TD]
[/TR]
[TR]
[TD]Monaco[/TD]
[/TR]
[TR]
[TD]Mons[/TD]
[/TR]
[TR]
[TD]Monterrey[/TD]
[/TR]
[TR]
[TD]Montpellier[/TD]
[/TR]
[TR]
[TD]Nacional[/TD]
[/TR]
[TR]
[TD]Nancy[/TD]
[/TR]
[TR]
[TD]Napoli[/TD]
[/TR]
[TR]
[TD]Náutico[/TD]
[/TR]
[TR]
[TD]Newcastle[/TD]
[/TR]
[TR]
[TD]NOB[/TD]
[/TR]
[TR]
[TD]Nordsjælland[/TD]
[/TR]
[TR]
[TD]Nottm Forest[/TD]
[/TR]
[TR]
[TD]Novara[/TD]
[/TR]
[TR]
[TD]Olympiakos[/TD]
[/TR]
[TR]
[TD]Paços Ferreira[/TD]
[/TR]
[TR]
[TD]Palermo[/TD]
[/TR]
[TR]
[TD]Panathinaikos[/TD]
[/TR]
[TR]
[TD]Parma[/TD]
[/TR]
[TR]
[TD]Peñarol[/TD]
[/TR]
[TR]
[TD]Pescara[/TD]
[/TR]
[TR]
[TD]Peterborough[/TD]
[/TR]
[TR]
[TD]Porto[/TD]
[/TR]
[TR]
[TD]PSG[/TD]
[/TR]
[TR]
[TD]PSV[/TD]
[/TR]
[TR]
[TD]Quindío[/TD]
[/TR]
[TR]
[TD]Rayyan[/TD]
[/TR]
[TR]
[TD]Reading[/TD]
[/TR]
[TR]
[TD]Real Madrid[/TD]
[/TR]
[TR]
[TD]Real Sociedad[/TD]
[/TR]
[TR]
[TD]Rennes[/TD]
[/TR]
[TR]
[TD]Reus[/TD]
[/TR]
[TR]
[TD]Roma[/TD]
[/TR]
[TR]
[TD]Rosenborg[/TD]
[/TR]
[TR]
[TD]Rubin[/TD]
[/TR]
[TR]
[TD]Saint-Étienne[/TD]
[/TR]
[TR]
[TD]Sampdoria[/TD]
[/TR]
[TR]
[TD]San Francisco[/TD]
[/TR]
[TR]
[TD]Santander[/TD]
[/TR]
[TR]
[TD]Santos[/TD]
[/TR]
[TR]
[TD]São Paulo[/TD]
[/TR]
[TR]
[TD]Sassuolo[/TD]
[/TR]
[TR]
[TD]Schalke 04[/TD]
[/TR]
[TR]
[TD]Servette[/TD]
[/TR]
[TR]
[TD]Sevilla[/TD]
[/TR]
[TR]
[TD]Shakhtar D[/TD]
[/TR]
[TR]
[TD]Siena[/TD]
[/TR]
[TR]
[TD]Sion[/TD]
[/TR]
[TR]
[TD]Slaven[/TD]
[/TR]
[TR]
[TD]Sounders[/TD]
[/TR]
[TR]
[TD]Spartak M[/TD]
[/TR]
[TR]
[TD]Split[/TD]
[/TR]
[TR]
[TD]Sporting CP[/TD]
[/TR]
[TR]
[TD]Sportul[/TD]
[/TR]
[TR]
[TD]Standard[/TD]
[/TR]
[TR]
[TD]Steaua[/TD]
[/TR]
[TR]
[TD]Stuttgart[/TD]
[/TR]
[TR]
[TD]Sunderland[/TD]
[/TR]
[TR]
[TD]Swansea[/TD]
[/TR]
[TR]
[TD]Swindon[/TD]
[/TR]
[TR]
[TD]Ternana[/TD]
[/TR]
[TR]
[TD]Torino[/TD]
[/TR]
[TR]
[TD]Tottenham[/TD]
[/TR]
[TR]
[TD]Toulouse[/TD]
[/TR]
[TR]
[TD]Twente[/TD]
[/TR]
[TR]
[TD]Udinese[/TD]
[/TR]
[TR]
[TD]Univ Chile[/TD]
[/TR]
[TR]
[TD]Universitario[/TD]
[/TR]
[TR]
[TD]Valencia[/TD]
[/TR]
[TR]
[TD]Vallecano[/TD]
[/TR]
[TR]
[TD]Varese[/TD]
[/TR]
[TR]
[TD]Vélez[/TD]
[/TR]
[TR]
[TD]Villarreal[/TD]
[/TR]
[TR]
[TD]Vitesse[/TD]
[/TR]
[TR]
[TD]Vitória[/TD]
[/TR]
[TR]
[TD]Vojvodina[/TD]
[/TR]
[TR]
[TD]West Bromwich[/TD]
[/TR]
[TR]
[TD]West Ham[/TD]
[/TR]
[TR]
[TD]Wigan[/TD]
[/TR]
[TR]
[TD]Wolverhampton[/TD]
[/TR]
[TR]
[TD]Young Boys[/TD]
[/TR]
[TR]
[TD]Zadar[/TD]
[/TR]
[TR]
[TD]Zaragoza[/TD]
[/TR]
</tbody>[/TABLE]
 
Try to complete the the team list and name the completed list TeamList. Then invoke:

B2:

=SUBSTITUTE(A2,C2,"")

C2:

=LOOKUP(9.99999999999999E+307,SEARCH(TeamList,A2),TeamList)
 
Upvote 0
Addendum. Modify B2 as follows:

=SUBSTITUTE(A2,C2,"",SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,C2,"")))/LEN(C2))

which handles the possible cases like Amadeo BarcelonaBarcelona.
 
Upvote 0
If... and it is a big IF... if all the player names are always two names with a single space between them, then this array-entered** formula (which does not need a separate "Team Name" list) appears to work for retrieving the player's name (assumed to be in cell B2)...

=LEFT(A2,FIND(" ",A2)+MIN(IF(ABS(CODE(MID(MID(A2,FIND(" ",A2)+2,999),ROW(INDIRECT("1:"&LEN(A2)-FIND(" ",A2)-1)),1))-77.5)<12.5,ROW(INDIRECT("1:"&LEN(A2)-FIND(" ",A2)-1)))))

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

You can use this formula to retriever the team name...

=SUBSTITUTE(A2,B2,"")
 
Upvote 0
=LEFT(A2,FIND(" ",A2)+MIN(IF(ABS(CODE(MID(MID(A2,FIND(" ",A2)+2,999),ROW(INDIRECT("1:"&LEN(A2)-FIND(" ",A2)-1)),1))-77.5)<12.5,ROW(INDIRECT("1:"&LEN(A2)-FIND(" ",A2)-1)))))

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
I think replacing the Volatile INDIRECT function with the non-Volatile INDEX function would be an improvement, so here it is...

=LEFT(A2,FIND(" ",A2)+MIN(IF(ABS(CODE(MID(MID(A2,FIND(" ",A2)+2,999),ROW(A$1:INDEX(A:A,LEN(A2)-FIND(" ",A2)-1)),1))-77.5)<12.5,ROW(A$1:INDEX(A:A,LEN(A2)-FIND(" ",A2)-1)))))
 
Upvote 0
I see now that the list of teams does not contain all teams you work with. You do not need to use/complete this list as long as the name of the team follows the name of the player without a space. Both names can have more than two parts. Enter with Ctrl-Shift-Enter in B1:

=LEFT(A1,MATCH(1,(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>" ")*(ABS(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1)))+1,1))-77,5)<=12,5),0))

in C1 (just Enter):

=SUBSTITUTE(A1,B1,"",1)
Excel Workbook
ABC
1Adam LallanaSouthamptonAdam LallanaSouthampton
2Ahmed ElmohamadyHullAhmed ElmohamadyHull
3Juan Carlos ValerDeportivo La CorunaJuan Carlos ValerDeportivo La Coruna
4David VillaAston VillaDavid VillaAston Villa
5Leon OsmanEvertonLeon OsmanEverton
6Mark NobleWest HamMark NobleWest Ham
7Mile JedinakCrystal PalaceMile JedinakCrystal Palace
8Simon MignoletLiverpoolSimon MignoletLiverpool
9Steve SidwellFulhamSteve SidwellFulham
10Steven CaulkerCardiffSteven CaulkerCardiff
11MessiBarcelonaMessiBarcelona
Sheet
 
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