Excel Formula problem

[Calexico]

Hi,

I'm working on a list of rugby results where column A contains the fixture "Team A v Team B".

I want to separate the teams from column A and put the teams into 2 cells. I've been messing about with the LEFT, RIGHT and FIND functions and managed separate the home team, but I get undesirable results for the away team.

EG!
Cell A2
"All Blacks v France"

Cell D2: home team
=LEFT(A2,FIND(" v ",A2)) Result= "All Blacks" <---Happy days!

Cell E2: Away team
=RIGHT(A2,FIND(" v ",A2)) Result= "ks v France" <-----Booo!


I've reached the ends of both my excel skills and my google search skills, advice gratefully appreciated.

 

[Daveangle]

You could do something along the lines of using Len to find the total characters in the list them sum that against the find. For instance:

=RIGHT(A1,SUM(LEN(A1)-FIND(" v ",A1)-1)) would give you France

It effectively finds the number where the " v " is then subtracts the total number of characters from it, you also need another -1 to get rid of the v, otherwise it will return v France instead of just France.

Have a go see if it works.

 

[Malakite]

you could just do a find and replace on the column

if you find and replace the middle section [space] v [space] and replace it with a ;

You can then just use the text to columns function within excel to split it

 

[Participant]

Look into the =MID formula.

=MID(A2,((FIND("v",A2))+2),99)

This is like =LEFT and =RIGHT except it allows you to define the start point of the formula.