+ Reply to Thread
Results 1 to 6 of 6

Removing Scores from Team Data with 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 Scores from Team Data with Text Functions

    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,

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Removing Scores from Team Data with Text Functions

    Maybe like this:
    Attached Files Attached Files

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

    Re: Removing Scores from Team Data with Text Functions

    Hi mrvp,

    I have transformed your both the scenarios in the attached file and tried solving your query, which seems to be done.
    Have a look into the spreadsheet and let us know if this is what your were looking for.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Attached Files Attached Files
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

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

    Re: Removing Scores from Team Data with Text Functions

    As Zbor was first, I tried the dave.xls solution and it was spot on (I like the sense of humour with the title) . I'm glad I asked or otherwise I would have never figured that formula out (I didn't even know that lookup vector's existed!!!). Problem solved and thanks.

    Not that it's critical but if anyone can explain the following formula that did the job (based on C1 for the Away Teams) I'd be grateful just so that I can learn something:

    =LEFT(C1,LEN(C1)-LEN(LOOKUP(99^99,--RIGHT(C1,ROW($A$1:$A$10))))-1) (I get the LEFT and LEN I think, I'm presuming left to go from the left end and len to analyse the team length but the rest of it is way over my head).

    Thanks again.

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Removing Scores from Team Data with Text Functions

    If there is a number at the end it will take it out: LOOKUP(99^99,--RIGHT(C1,ROW($A$1:$A$10)))
    Then you need a LEN to see how long is that number and with LEFT function take whole word (LEN(C1) minus LEN of the last number).

    If you don0t mind having space at the end of the name you can remove -1. Also, removing that -1 will preserve you full word, for example now:

    XY XY 23 will return XY XY
    but
    XY XY23 Will return XY X

    and if you put =LEFT(C1,LEN(C1)-LEN(LOOKUP(99^99,--RIGHT(C1,ROW($A$1:$A$10)))))

    you'll get
    XY XY_ (<- space)
    and
    XY XY

    Also, this formula will return you result if there is number in the name, like:

    George Bush 2 12
    result
    George Bush 2

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

    Re: Removing Scores from Team Data with Text Functions

    Thanks, I think I get how that works now. I'll definitely be keeping the -1 in, as spaces tend to be the thing that throw VLookups out (when you are pasting data from one source without a space, into something that does have spaces etc.). This has been really helpful. Many thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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