Excel question

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

    #1
    Excel question
    Have pitcher name
    T STAUFFER-R
    T STAUFFER-L

    Want to get rid of "-R" or "-L" in a cell and just capture the Name
    When pitchers change you dont know if it will end with the L or R
  • FourLengthsClear
    SBR MVP
    • 12-29-10
    • 3808

    #2
    If your pitcher name is in cell A1 then

    =SUBSTITUTE(A1,RIGHT(A1,2),"")
    Comment
    • Formulawiz
      Restricted User
      • 01-12-09
      • 1589

      #3
      Originally posted by FourLengthsClear
      If your pitcher name is in cell A1 then

      =SUBSTITUTE(A1,RIGHT(A1,2),"")

      Does not work. I have a name such as T WOOD-L.
      Your formula does not get rid of the "-L"
      Comment
      • FourLengthsClear
        SBR MVP
        • 12-29-10
        • 3808

        #4
        Originally posted by Formulawiz
        Does not work. I have a name such as T WOOD-L.
        Your formula does not get rid of the "-L"
        It works fine for me, are you sure you entered it correctly?

        Comment
        • goucla
          SBR MVP
          • 09-11-10
          • 1287

          #5
          r u using this to determin just win % against opp or is this a more complicated system?
          Comment
          • WendysRox
            SBR High Roller
            • 07-22-10
            • 184

            #6
            Originally posted by FourLengthsClear
            If your pitcher name is in cell A1 then

            =SUBSTITUTE(A1,RIGHT(A1,2),"")
            works for me, bro. Thanks or teaching me a new trick!
            Comment
            • mrtomk
              SBR High Roller
              • 02-24-11
              • 105

              #7
              The above works nicely for me too, alternatively =LEFT(A1,LEN(A1)-2)
              Comment
              • brewers7
                SBR Sharp
                • 03-11-06
                • 298

                #8
                Originally posted by Formulawiz
                Have pitcher name
                T STAUFFER-R
                T STAUFFER-L

                Want to get rid of "-R" or "-L" in a cell and just capture the Name
                When pitchers change you dont know if it will end with the L or R

                Formula:

                An easier way to fix it is to do this:

                Do a CTRL-H and you get the Find/Replace dialog box...

                In the Find box, type:

                T STAUFFER-R

                In the Replace Box, type:

                T STAUFFER

                Then hit the "Replace All" button, and voila...

                Then do it again, but changing the R to an L and then hit "replace all" again, and you are done...

                GL...
                Comment
                • LT Profits
                  SBR Aristocracy
                  • 10-27-06
                  • 90963

                  #9
                  Or you can Parse by doing Data/Text to Columns/Delimited/Other "-"
                  Comment
                  • mrtomk
                    SBR High Roller
                    • 02-24-11
                    • 105

                    #10
                    Originally posted by brewers7
                    Formula: An easier way to fix it is to do this: Do a CTRL-H and you get the Find/Replace dialog box... In the Find box, type: T STAUFFER-R In the Replace Box, type: T STAUFFER Then hit the "Replace All" button, and voila... Then do it again, but changing the R to an L and then hit "replace all" again, and you are done... GL...
                    This isnt easier if you have a whole list of pitchers. If anything do find and replace on -R replaced with blank. You'll also have to repeat this if you ever add to the list, instead of copying the formula down.
                    Comment
                    • uva3021
                      SBR Wise Guy
                      • 03-01-07
                      • 537

                      #11
                      Doing a find and replace might take 3 seconds longer than copying the formula down, but it saves on resources because you don't have any reoccurring calculations. You can do a find and replace on open, which would remove any key strokes.

                      At some point you'll have so many calculations on your sheet its hard to even navigate the data.
                      Comment
                      • mrtomk
                        SBR High Roller
                        • 02-24-11
                        • 105

                        #12
                        Fair enough, they are both valid. It really depends on the amount of data he is using and how/how often it needs updating and manipulating.
                        Comment
                        • ScreaminPain
                          SBR High Roller
                          • 09-17-08
                          • 246

                          #13
                          Originally posted by Formulawiz
                          Does not work. I have a name such as T WOOD-L.
                          Your formula does not get rid of the "-L"
                          The formula suggested doesn't work for you because you have a period after the "L". either remove the period or change the "2" in the formula to "3"......works perfectly!
                          Last edited by ScreaminPain; 04-14-11, 02:20 PM.
                          Comment
                          • Pokerjoe
                            SBR Wise Guy
                            • 04-17-09
                            • 704

                            #14
                            Find: -*
                            replace with a blank.

                            This way you don't have to worry about whether there's a period after the last letter, and you don't have to make separate searches for L and R.
                            Comment
                            • Formulawiz
                              Restricted User
                              • 01-12-09
                              • 1589

                              #15
                              Works fine. My error
                              Thanks guys
                              Comment
                              SBR Contests
                              Collapse
                              Top-Rated US Sportsbooks
                              Collapse
                              Working...