Trimming text from a sheet with teams and scores of games

Hi,

I've got one of my usual kind of queries, but I've learned something from the last query that I had involving trimming text but I'm still running into grief with trimming exactly what I want out of the data.

In short teams are now coming in like this (in one cell):

Großwallstadt vs. Melsungen

I'm trimming the spaces out with a (assuming the above is in B2):

=SUBSTITUTE(CLEAN(TRIM(B2)),CHAR(160),"")

I'm then trimming the teams out with a:

=TRIM(LEFT(SUBSTITUTE(F2," vs. ",REPT(" ",LEN(F2))),LEN(F2)))

and a

=TRIM(MID(SUBSTITUTE(F2," ",REPT(" ",LEN(F2))), 2*LEN(F2),LEN(F2)))

and then trimming the spaces out from around the results.

Scores are coming in like 22:24 (in column M)

so I'm going with:

=TRIM(LEFT(SUBSTITUTE(M2,":",REPT(" ",LEN(M2))),LEN(M2)))

and

=TRIM(MID(SUBSTITUTE(M2,":",REPT(" ",LEN(M2))), LEN(M2),LEN(M2)))

I have attached a copy of the xls for reference. In short, the above formulas seem to be working okay but not perfectly (which makes me think, I may have not referenced something properly and am possibly picking up spaces as well?). The team formulas seem to work okay generally but don't seem to handle teams with spaces in them, and on one line it's actually taken a vs. instead of a team (I've highlighted the lines in red where it's thrown up issues). I'm aware that I could use a left and a right on the score cells as most of the time the scores will only be double digit, but I'm just looking for a clean way to handle single or triple digit scores.

Any advice or pointers would be appreciated.

Thanks in advance,

Bundesliga 12_13 Results_Workup.xls

Hello VP,

Try these formula.

Home Team,

=TRIM(LEFT(B2,FIND(" vs.",B2&" vs.")-1))

Away Team,

=TRIM(MID(B2,FIND(" vs.",B2&" vs.")+4,250))

Then copy down.

Hi Haseeb,

These seem to work fine (either going straight from the B2 cell or from the cell with the =substitute(clean.... formula in it (I'll probably run it from the substitute line, as I know it will have cut any unnecessary spaces out).

Out of interest I virtually duplicated the formula for the scores that were originally in column C (and then I will trim them afterwards);

I'm going with the following for home scores;

=TRIM(LEFT(C2,FIND(":",C2&":")-1))

and this for away scores;

=TRIM(MID(C2,FIND(":",C2&":")+1,2))

I take it I have got the logic right?