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:

      [ALL$]:

      LOAD [Branch],

        [2016],

        [2015],

        [2014],

        [2013],

        [2012],

        [2011],

        [2010]

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

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

       

      Then I do a crosstable:

      Years:

      Crosstable (Year, measure, 1)

        LOAD *

        

          Resident [ALL$];

         

      Drop Table [ALL$];

       

      Then I am attempting to do the Rank:

      Rankings:

      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;