Hi,
I am looking to analyse some more Sports Data, and where I have grabbed it from a website the team name has come with the score after it (I know dependent on how the website is laid out sometimes things will paste nicely - but in this case it didn't). I have isolated the scores using RIGHT(B1) for the Home Teams and RIGHT(C1) for the Away Teams and created a Home Scores and Away Scores Column on a separate sheet. Now I am trying to isolate the Teams Names. I just intend to set this up in a spare column and copy the data onto a clear work sheet - so I have a clean sheet to do my analysis on. The Home Scores are in Column B1 and the Away Scores are in Column C1. The obvious problems are inconsistent team name lengths (e.g. so I couldn't use LEFT and set it for the first 10 characters) and some teams have several spaces in them (e.g. Thomas Sabo Ice Tigers 2 (with the two being the score)) - and I think spaces cause problems with text functions?
I was figuring that this is possible with a formula and a bit of copying and pasting and should be the something like the reverse of the formula where the score or ranking was at the front and in brackets:
e.g. for (4) Wisconsin in cell D51
=IF(ISNUMBER(SEARCH(")",D51)),TRIM(MID(D51,SEARCH(")",D51)+1,30)))
with 30 being the right number to ensure teams of varying name length had all of their team name included in the adjustment cell.
I am going to have a go at this, but I would greatly appreciate any advice that anyone could offer.
Many thanks,
Bookmarks