Hi,
I'm trying to manipulate some data and have almost got it the way that I want to get it but I am struggling to get over the final hurdle so to speak. I am looking to get a trimmed set of data with no unwanted spaces that I can then copy onto a clean sheet to do what I want to do with it.
The data is for the 2011/2012 KHL Season.
The key columns for me are A, B and C (I wasn't interested in D, E and F and G was an index column I put in to sort out unnecessary lines with dates on them which I have deleted out and then to invert the data into chronological order - the site I grabbed this off of had the data with the latest games first).
Columns I and J are the team names in A and C trimmed of unnecessary spaces (columns K and L were a check against the original data) in column M, I trimmed the scores in column B (as when I originally tried =left,B2,1 or =right,B2,1 etc. - it was returning blanks implying that there were spaces there e.g. when I then tried right,B2,2 it returned the score implying the space beyond the score).
My problem is how do I take the text out of column M? E.g. on line 3 there is an overtime and penalties game which means O3 returns a ")" instead of a score. I don't think any of the games go into a double figures so for the time being a left,Mx,1 or right,Mx,1 should do for non-overtime games but I can't figure out how to remove the text.
Any advice or nudges in the right direction would be appreciated. I'm thinking I might be able to use a LEN formula with a LOOKUP perhaps (for identifying numbers instead of text)?
Thanks,
KHL_11_12_Workup.xls
Bookmarks