4 Replies Latest reply: May 15, 2014 9:53 AM by donh _ RSS

    padding problems

    donh _

      I have been trying just about everything and can't seem to get the last piece - - -

       

      id1 column needs to be id2 with 5 characters - 3 to the left of the decimal (if there is a decimal) and 2 to the right of the decimal (unless it begins with the letter E then it is just 1 to the right).  Cells that are blank/empty need to be blank/empty:

       

      [font="courier"]id1     id2

            

      E923     E9230

      E849.5      E8495

      642.21     64221

      997.5     99750

      998     99800

            

      9     00900

      11.8     01180

      V05.3     V0530

      V02.51     V0251[/font][/quote]my formula so far does not put the zero's in the correct place for xxx.x data --- it resuls xxx.0x

       

      [font="courier"]if((Len())=0,chr(0),

      if((Left(,1))="E",

      (LSplit(,2,".",1)right("0"trim(LSplit(,2,".",2)),1)),

      LSplit(,2,".",1)left("00"trim(LSplit(,2,".",2)),2)))[/font][/quote]Can anyone help?

       

      Thanks

       

      Don

        • padding problems
          Data Kruncher

          /size[quote]code:[/size]if(left(id1,1)="E",

          left(Strip(id1,".")+"00000",5),

          if(instr(".",id1)>0,

          right("00000"lsplit(id1,2,".",1)left(rsplit(id1,2,".",1)+"00",2),5),

          if(instr(".",id1)=0,right("00"id1"00",5),right("00000"lsplit(id1,2,".",1)left(rsplit(id1,2,".",1)+"00",2),5)

          ))) /QUOTEMy brain hurts!        

           

          This works for all of your sample data.

           

          If someone thinks there's a better way, be my guest. Please. Simplify this monster.

           

          Edit: I missed the "blank must be blank" requirement.

           

          The expression then becomes:

          /size[quote]code:[/size]if(id1="","",if(left(id1,1)="E",

          left(Strip(id1,".")+"00000",5),

          if(instr(".",id1)>0,

          right("00000"lsplit(id1,2,".",1)left(rsplit(id1,2,".",1)+"00",2),5),

          if(instr(".",id1)=0,right("00"id1"00",5),right("00000"lsplit(id1,2,".",1)left(rsplit(id1,2,".",1)+"00",2),5)

          ))))  /QUOTE

          • padding problems
            donh _

            PERFECT - - - BIG THANKS for your time

             

            Don

            • padding problems
              Bill Watson

              I think the following might also give you the result you are looking for:

               

              [font="courier"]replace(if(left(ID1,1).in.("E","V"),left(strip(ID1,".")+"00000",5),str(val(ID1)*100,5,0,"0")),"00000","")[/font][/quote]

              • padding problems
                donh _

                Bill:

                 

                That did work too

                 

                THANKS

                 

                Don