Negative Numbers with IF Statements

[kaitcoen]

I have income statement data that contains negative numbers (marked with a "-" in front of the number). These numbers are currently V_WString as they have "-" and commas. I need to use an IF statement based on if the number is negative but am having trouble figuring out the best data type/way to get to this. I currently have a Data Cleansing tool to remove punctuation followed by a Select tool to change the data type to Double followed by a Formula tool to write the IF statement. I noticed though that by using the Data Cleansing tool it also removed the negative sign (which makes sense) and therefore I'm not getting the "correct" answer from my IF statement because all the numbers are now positive.

What data type makes the most sense to retain the negative/positive of my numbers but allows me to write a conditional formula and is there a different way to do this? I have struggled using the Double data type because it truncates the number at the first sign of a comma, so this has been an area of frustration.

[patrick_digan · Accepted solution]

@kaitcoen I would use a function like this to convert it to a usable number:

tonumber(replace([Field],",",""))

Basically replace the commas with nothing (deletes the commas) and then convert it to a numeric datatype. 

[kaitcoen]

@patrick_digan  Thank you!

[Loic]

also if the only thing you are interested in is to know if the "number" is negative, you can simply test if the first character of your string is "-". use this: Left([StringField], 1) in your IF condition.

If you actually need to manipulate and do calculations with the number corresponding to the string then use tonumber(replace([StringField],",","")) as suggested by @patrick_digan