Results 1 to 10 of 10

Removing text from scores in a data sheet using text functions

Threaded View

  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

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