Error: Negative Numbers - Text to Number Creating Nulls or Zeros

[Jpschnee]

Hey All,

I am working with a large set of data, and the file we received is a csv so all data formats are default as v_wstring. This is creating negative umbers to be shown as (100) instead of -100. Since doubles do not accept () they are being forced as nulls or errors. Does anyone have a quick formula for converting this entire column to a number format while keeping the negative values? 

I have seen forums where people want the opposite of this but any insight would be helpful.

Thank you,

Jordan

[christine_assaad · Accepted solution]

Hi @Jpschnee 

Hope the below helps. the "Select" at the end is to change the type to Double. Cheers

[Jpschnee]

thank you!!!

[MarqueeCrew]

@Jpschnee,

I'd use a multi-field formula so you can change the TYPE as part of the configuration to a DOUBLE.

ToNumber(IIF(left([_CurrentField_],1)="(","-"+replacechar([_CurrentField_],"()",''),[_CurrentField_]))

Cheers,

Mark