3 Replies Latest reply on Nov 6, 2018 9:32 AM by Kendra Allenspach

    Wrong values when I convert string to integer

    malou.puyod

      I need to get the MBPS value from a field which is string.  The number value that I will extract will be used to get the % Utilization so I'm converting the string to integer.  I was able to convert it to integer BUT majority of the values I get are incorrect - sometimes double and a few is triple the value. 

       

      I need to get the number value from a string preceding the word MBPS or MB.

       

      Sample:  IC-AAZ R. AVENUE 4MBPS  ----> value should be 4

                    IC-BBB 5MB  ---> should be 5

       

      Here is my calculated field in order to get the number:

      Circuit Usage =

      IF CONTAINS([CIRCUIT ID], 'Mbps') THEN 

      TRIM(MID([CIRCUIT ID], FIND([CIRCUIT ID], 'Mbps') -2,2))

      ELSEIF CONTAINS([CIRCUIT ID], 'MBPS') THEN 

      TRIM(MID([CIRCUIT ID], FIND([CIRCUIT ID], 'MBPS') -2,2))

      ELSEIF CONTAINS([CIRCUIT ID], 'mbps') THEN 

      TRIM(MID([CIRCUIT ID], FIND([CIRCUIT ID], 'mbps') -2,2))

      ELSEIF CONTAINS([CIRCUIT ID], 'MB') THEN 

      TRIM(MID([CIRCUIT ID], FIND([CIRCUIT ID], 'MB') -2,2))

      ELSEIF CONTAINS([CIRCUIT ID], 'mb') THEN 

      TRIM(MID([CIRCUIT ID], FIND([CIRCUIT ID], 'mb') -2,2))

      ELSE ''

      END

       

      The above formula is correct because it displays the correct value however in string form.  So here's my 2nd formula which is displaying wrong values:

      INT([Circuit Usage])

       

      I also tried FLOAT([Circuit Usage]) but I also have the same problem.

       

        

      CORRECTCircuit Capacity
      48
      816
      1020
      1831
      55
      612
      612
      1016
      44
      55
      816
      3070

       

      Attached is my sample packaged workbook.  I hope someone can help me on this.

       

       

      Regards,

      Lou