2 Replies Latest reply: Mar 29, 2017 10:49 AM by Sunny Talwar RSS

    Remove % sign

    Mark Ritter

      I have an excel spreadsheet where the measures show for example 10.99% in the cells.


      I think that I need to get rid of the % sign in order to do calculations with these cell values but I am not sure of the best way to do this.


      This is what I am coding in the script and it is not working correctly because the values in the cells are not numeric:




      The entire process so far is this:

      Load excel sheet:


      LOAD [Branch],








      FROM [lib://AttachedFiles/Branch CM Modified.xls]

      (biff, embedded labels, table is ALL$);


      Then I do a crosstable:


      Crosstable (Year, measure, 1)

        LOAD *


          Resident [ALL$];


      Drop Table [ALL$];


      Then I am attempting to do the Rank:


      Load *,


      If (measure<'0','Negative',

         If (measure >='0' and measure <'5','0%<5%',

           If (measure >='5' and measure <'10','5%<10%')))

        as Rank

        Resident Years;