+ Reply to Thread
Results 1 to 10 of 10

Removing text from scores in a data sheet using text functions

  1. #1
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Question 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
    Last edited by mrvp; 07-15-2012 at 01:43 PM. Reason: solved

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Removing text from scores in a data sheet using text functions

    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>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Removing text from scores in a data sheet using text functions

    Hi,

    Thanks for this the suggested formula works fine (and I will mark this solved later). Just for my own understanding I take it the =TRIM(LEFT(M2,2)) is to allow for a double digit score with the left(m2,2) and the trim command cuts off the space if it is only one digit? With the other formula I am figuring the search is in their to effectively set the trim to the right of the ":" with the +1 to account for the space between the : and the number? Sorry for asking a further question I just want to try and fully understand the process for myself.

    Thanks,

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Removing text from scores in a data sheet using text functions

    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>

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,518

    Re: Removing text from scores in a data sheet using text functions

    Hi mrvp,

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

    Hope this helps.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Removing text from scores in a data sheet using text functions

    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>

  7. #7
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Removing text from scores in a data sheet using text functions

    Thanks to the pair of you. I just wanted to understand this a touch further so I didn't have to come back and ask a slightly dumb question on the same topic essentially (as for instance basketball scores go into three figures so I could see myself needing to trim to the right of a : for more than one number). Thanks again very helpful.

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Removing text from scores in a data sheet using text functions

    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>

  9. #9
    Forum Contributor
    Join Date
    11-11-2011
    Location
    London England
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    157

    Re: Removing text from scores in a data sheet using text functions

    Definitely will do - I just wanted to get an idea of how the things worked for my own benefit and anyone else's benefit (should someone look up a thread like this for a similar problem). if you know what I mean. I think both solutions are fairly elegant - I would need to look at MarvinP's in a bit more detail (figured a LEN could be used but just wasn't sure what went around it and have never used a REPT - every day a new function ). Thanks again to the pair of you.

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,518

    Re: Removing text from scores in a data sheet using text functions

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1