seperating text to columns without spacing or punctuation

[jubby059]

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]

 

[Aladin Akyurek]

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)

 

[Aladin Akyurek]

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.

 

[azumi]

Im givin you the solution in this links, hope this works :

https://www.dropbox.com/s/rnhxfch39evqx0r/Example-Az.xlsx?dl=0

 

[jubby059]

thanks guys! :D

 

[Aladin Akyurek]

thanks guys! :D

I'd advise to opt for a more efficient and correct set up...

Just in case, here is the workbook that implements the set up:
https://dl.dropboxusercontent.com/u/65698317/jubby059%20separating%20team%20and%20name.xlsx

 

[Rick Rothstein]

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,"")

 

[Rick Rothstein]

=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)))))

 

[István Hirsch]

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
	A	B	C
1	Adam LallanaSouthampton	Adam Lallana	Southampton
2	Ahmed ElmohamadyHull	Ahmed Elmohamady	Hull
3	Juan Carlos ValerDeportivo La Coruna	Juan Carlos Valer	Deportivo La Coruna
4	David VillaAston Villa	David Villa	Aston Villa
5	Leon OsmanEverton	Leon Osman	Everton
6	Mark NobleWest Ham	Mark Noble	West Ham
7	Mile JedinakCrystal Palace	Mile Jedinak	Crystal Palace
8	Simon MignoletLiverpool	Simon Mignolet	Liverpool
9	Steve SidwellFulham	Steve Sidwell	Fulham
10	Steven CaulkerCardiff	Steven Caulker	Cardiff
11	MessiBarcelona	Messi	Barcelona
Sheet