Excel 2000 help

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Formulawiz
    Restricted User
    • 01-12-09
    • 1589

    #1
    Excel 2000 help
    If you have a cell with the values of 12-9 which reflects a win/loss record. How do you extract the value 12 and the the value 9 without including the dash. Remember at the beginning of the season you may have a record of 1-1 and then when the season progresses the values may go to 10-10. How does Excel automatically recognize the vales going to 2 digits.
    I am looking for a formula such as this
    =VALUE(LEFT(ARI!B44,1))
    =Mid(A2,Find(“-“,A2)+1,2)
    The problem with these you have to change the value from 1 to 2 at the end of formula 1 manually. I want the formula to recognize if the value has a double digit such as 10 vs. a single digit number such a 9.
    Thanks
    Last edited by Formulawiz; 09-06-10, 06:40 PM.
  • Miz
    SBR Wise Guy
    • 08-30-09
    • 695

    #2
    my reply doesn't apply to the updated post
    Last edited by Miz; 09-06-10, 06:46 PM.
    Comment
    • roasthawg
      SBR MVP
      • 11-09-07
      • 2990

      #3
      Select the column with the data in it. Go to "data" and then select "text to columns". Select "delimited" and then place a check mark in the box marked "other". Enter in a "-" in the box and select finish. Make sure you have an empty column next to the the column that you are delimiting or it will overwrite the column.

      Alternatively you can extract the text with from cell "A1" with a function like this:
      =LEFT(A1,FIND("-",A1)-1)*1 (gives you the 12 wins)
      =RIGHT(A1,LEN(A1)-FIND(".",A1))*1 (gives you the 9 losses)

      This is with a newer version of excel but both methods should still work for you. Good luck!
      Last edited by roasthawg; 09-06-10, 07:12 PM.
      Comment
      • Emily_Haines
        SBR Posting Legend
        • 04-14-09
        • 15917

        #4
        try this.......................

        =REPLACE(A1,FIND("-",A1),1,"")
        Comment
        • Formulawiz
          Restricted User
          • 01-12-09
          • 1589

          #5
          Originally posted by roasthawg
          Select the column with the data in it. Go to "data" and then select "text to columns". Select "delimited" and then place a check mark in the box marked "other". Enter in a "-" in the box and select finish. Make sure you have an empty column next to the the column that you are delimiting or it will overwrite the column.

          Alternatively you can extract the text with from cell "A1" with a function like this:
          =LEFT(A1,FIND("-",A1)-1)*1 (gives you the 12 wins)
          =RIGHT(A1,LEN(A1)-FIND(".",A1))*1 (gives you the 9 losses)

          This is with a newer version of excel but both methods should still work for you. Good luck!
          Thank you the formulas worked great
          Comment
          SBR Contests
          Collapse
          Top-Rated US Sportsbooks
          Collapse
          Working...