LOS Statements - Converting $0.00 and -$0.00 from string to number format

[jrkmorgan]

I have an LOS statement with 60 columns of data - 7 columns need to be numeric (volumes) and the remaining 53 are revenue/expense columns.

All of them are coming in as v_string.

I need them all to be numbers to perform calculations.

The revenue/expense columns do have dollar signs, decimal, and some negative signs.

I've tried TONUMBER in several ways, but I'm not getting it to work.

I'm also getting ConvError: Select (89): Revenue Value NGL: Field Conversion Error Limit Reached and ConvError: Select (89): Revenue Value NGL: $0.00 is not a valid number.

Sample set attached.

[Felipe_Ribeir0 · Accepted solution]

Hi @jrkmorgan 

If you replace '$' by '' before converting the field to numeric, it will work. You have many columns, so the best way to do it is using the multi field formula.

[PanPP]

Hi @jrkmorgan 

It's good to know that sometimes you may want to remove the comma as well in a number ( 1,100 --> 1100) using a similar approach.