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

# Wrong values when I convert string to integer

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.

 CORRECT Circuit Capacity 4 8 8 16 10 20 18 31 5 5 6 12 6 12 10 16 4 4 5 5 8 16 30 70

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

Regards,

Lou

• ###### 1. Re: Wrong values when I convert string to integer

Hi Lou,

The reason you are seeing double or triple is because you're using SUM and you have two or three rows for the same Circuit ID.  For example, for "BDO CM RECTO 10Mbps", if you view the data, then click on "Underlying", you'll see there are two rows, so the sum is 20 rather than 10.  If you have three rows, you'd get triple the value.

One way to fix this is to use MIN instead of SUM.  Since all rows for the same Circuit ID should have the same value, you can use MIN or MAX and the result would be the same.

1 of 1 people found this helpful
• ###### 2. Re: Wrong values when I convert string to integer

Thanks Jeff!    I will apply your solution.  I'm not sure if using Min or Max will also result to correct values if there are multiple circuits with XX Mbps values per customer in the future.

• ###### 3. Re: Wrong values when I convert string to integer

This solution just helped me too! Thank you for asking malou.puyod and for the explanation/answer Jeff D!!