+ Reply to Thread
Results 1 to 10 of 10

Extracting Values from a String of Scores

  1. #1
    Registered User
    Join Date
    07-27-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    12

    Extracting Values from a String of Scores

    Hi,

    I've got a load of tennis data in the below format and I'm trying to calculate how many games each player won (excluding the numbers in brackets). Does anyone have a quick/clever way of isolating the games before the hyphens in one column and the numbers after the columns so I can do to this? Any help much appreciated. If I do text to columns to split on the "-" Excel turns everything into dates.

    Thanks

    Sid

    7-6(6) 6-2 7-6(3)
    6-3 6-1 6-1
    6-2 6-3 6-3
    6-4 6-4 6-4
    6-1 6-2 6-4
    6-2 6-3 6-2
    7-6(7) 4-6 6-2 6-3
    6-1 2-6 6-4 6-2
    3-6 7-6(3) 6-3 7-5
    5-7 6-3 6-0 7-5
    6-1 3-6 5-7 6-4 6-1
    6-3 6-3 4-6 2-6 6-3
    7-5 6-1 7-6(5)
    6-2 7-6(8) 6-0
    2-6 6-7(1) 7-5 6-2 6-2
    7-5 6-4 6-2
    6-4 6-3 7-6(3)
    6-4 3-6 6-0 7-5
    6-2 6-2 7-6(2)
    7-5 6-2 6-3
    7-6(5) 4-6 6-2 6-2
    7-5 6-3 6-3
    6-3 6-4 6-4
    6-2 6-3 6-2
    6-3 3-6 2-6 6-3 6-4
    7-5 7-6(4) 4-6 6-0

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Extracting Values from a String of Scores

    Can you post a sample workbook, showing expected results.

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Registered User
    Join Date
    07-27-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Extracting Values from a String of Scores

    I think I attached a sample, can you let me know if it worked please?
    Attached Files Attached Files

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Extracting Values from a String of Scores

    With T2C with "Space" as delimiter and in Step 3 set all columns from General to Text

    D
    E
    F
    G
    H
    I
    J
    K
    1
    Score1
    Score2
    Score3
    Score4
    Score5
    P1
    P2
    Formula
    2
    7-6(6) 6-2 7-6(3)
    20
    14
    P1: {=SUM(IFERROR(--LEFT(D2:H2),0))}
    3
    6-3 6-1 6-1
    18
    5
    P2: {=SUM(IFERROR(--MID(D2:H2,3,1),0))}
    4
    6-2 6-3 6-3
    18
    8
    both array entered
    5
    6-4 6-4 6-4
    18
    12
    6
    6-1 6-2 6-4
    18
    7
    7
    6-2 6-3 6-2
    18
    7
    8
    7-6(7) 4-6 6-2 6-3
    23
    17
    9
    6-1 2-6 6-4 6-2
    20
    13
    10
    3-6 7-6(3) 6-3 7-5
    23
    20
    11
    5-7 6-3 6-0 7-5
    24
    15
    12
    6-1 3-6 5-7 6-4 6-1
    26
    19
    13
    6-3 6-3 4-6 2-6 6-3
    24
    21
    14
    7-5 6-1 7-6(5)
    20
    12
    15
    6-2 7-6(8) 6-0
    19
    8
    16
    2-6 6-7(1) 7-5 6-2 6-2
    27
    22
    17
    7-5 6-4 6-2
    19
    11
    18
    6-4 6-3 7-6(3)
    19
    13
    19
    6-4 3-6 6-0 7-5
    22
    15
    20
    6-2 6-2 7-6(2)
    19
    10
    21
    7-5 6-2 6-3
    19
    10
    22
    7-6(5) 4-6 6-2 6-2
    23
    16
    23
    7-5 6-3 6-3
    19
    11
    24
    6-3 6-4 6-4
    18
    11
    25
    6-2 6-3 6-2
    18
    7
    26
    6-3 3-6 2-6 6-3 6-4
    23
    22
    27
    7-5 7-6(4) 4-6 6-0
    24
    17
    Sheet: T2C

    and update your profile about Excel version. Excel 2003 is not compatible with extension of your file
    Last edited by sandy666; 06-26-2018 at 06:19 AM. Reason: formulas correction

  5. #5
    Registered User
    Join Date
    07-27-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Extracting Values from a String of Scores

    I tried that but I get dates (eg 06-Mar for 6-3).

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Extracting Values from a String of Scores

    did you read what I said?
    ALL columns from General to Text, in step 3 scroll down and you'll see more than 3 columns then set each col to text.

    t2c.jpg
    Last edited by sandy666; 06-26-2018 at 03:21 AM.

  7. #7
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Extracting Values from a String of Scores

    You have to set the T to C settings to TEXT instead of general!

    I am working on the formula to get the games, but I'm not quite there yet.

    Got it! =SUM(VALUE(IFERROR(LEFT(A1:E1,1)*1,0)))

    I hope this helps, please let me know!

    Regards,

    David

    If this has been helpful
    - Please click on the *Add Reputation button at the bottom left.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

    Last edited by David A Coop; 06-26-2018 at 03:32 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Extracting Values from a String of Scores

    Rule 08: Cross-posting Without Telling Us

    Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    If you have less than 10 posts, do not try to copy and paste the link. Instead, type the link out in your thread.

    No further help to be offered, please, until the OP has complied with this request.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  9. #9
    Registered User
    Join Date
    07-27-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Extracting Values from a String of Scores

    Many thanks for your help, I managed to fix it as a result.

    Much appreciated.

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Extracting Values from a String of Scores

    Are you talking to ... ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extracting Specific Values from a Text string
    By kollur in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-04-2015, 02:22 PM
  2. [SOLVED] I need a macro to select range of scores, choose the best scores and apply a formula
    By hadleedog in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2015, 03:15 PM
  3. Extracting numeric values from string
    By RJF in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-03-2014, 12:57 AM
  4. Replies: 12
    Last Post: 03-20-2013, 05:46 PM
  5. [SOLVED] Extracting values from a string with VBA
    By BaconOke in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2012, 08:52 AM
  6. [SOLVED] extracting values from string
    By Alexander_Golinsky in forum Excel General
    Replies: 13
    Last Post: 06-20-2012, 07:33 AM
  7. Extracting Numeric Values from an Alpha/Numeric String
    By Delkath in forum Excel General
    Replies: 5
    Last Post: 10-27-2010, 02:36 PM

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