Removing text from scores in a data sheet using text functions

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

Hi mrvp,

For Home score, use :-

=TRIM(LEFT(M2,2))

and for Away Score, use:-
=TRIM(MID(M2,SEARCH(":",M2)+1,2))

See attached:- KHL_11_12_Workup.xls

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi mrvp,

Your understanding is correct for first formula i.e., =TRIM(LEFT(M2,2))

Second i.e, (=TRIM(MID(M2,SEARCH(":",M2)+1,2))) is explained below:-

SEARCH(":",M2) searches the position of : in the Score 5 : 3 which comes to 3

MID(M2,SEARCH(":",M2)+1,2) extracts the 2 digits starting from the position where it found ":" in above formula i.e., two characters
starting from 3 position i.e., " 3"
and then trim removes the extra space


Just figured out that you can have 3 characters and hence can use the formula:-
=TRIM(MID(M2,SEARCH(":",M2)+1,3)) in column O

Let me know if this helps


regards,
DILIPandey

<click on below 'star' if this helps>

Hi mrvp,

See the attached with some formulas that will work better than the ones above.

Hope this helps.

Hi mrvp,

I checked and compared these formulas with above ones and found that basis your data arrangement, they also provide the same results and yet using less complexity and less resources (i.e, number of formulas & nesting ) choice is yours.. thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

Cheers

If you are happy with the help provided, suggest you to mark this thread as [SOLVED].. thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

Hey mrvp,

My formula will handle 3 or 4 digit scores. Here is what it does.

The formula replaces every space in a string with a bundh of spaces. The number of spaces is determined by how long the original string is.
I call this "Blowing Up the Spaces".

The reason to do this is we know that the TRIM() function will knock them all off again.

After the string like "I like you" is turned into "I like you", I can grab the middle word by using a Mid() function. If I start at the length of the original string out and grab a lenght of original string from it and trim the middle part, I'm left with "like".

This method works for multi-word stings that have spaces in them where each substring is of different or variable length.

I was worried that dilipandey's formula would not deal with a first score of 234 : 456 as his Left(A1,2) didn't look for a space. This is why this method is a little more robust and possible better.