Remove decimal from amount

[rockeylearning]

Hi!

How can I remove the decimal from amount but still keep the cent? For example, 2222.22, I want it to be 222222.

I found Replace([Field1], ',', ''), it works if my number is 2,222.22 which remove the comma. but if my amount doesn't have a comma, it error out.

I even tried ToNumber(Replace(Replace([Field1], " .", ""), ",", "")), but this remove the cent, which I still need the cent. 

Thank you for your help!

[PhilipMannering]

Looks like you're field is already numerical, so can't you multiple it by 100? Then change the data type to an integer if you don't want to see any decimal values.

[RobertOdera]

Hi, @rockeylearning 

Kindly consider the following arguments.

IF CONTAINS([Field1],'.') THEN Replace([Field1],'.','')

ELSEIF CONTAINS([Field1],',') THEN Replace([Field1],',','')

ELSE [Field1]

ENDIF

OR

Use the Data Cleansing tool and select the option to remove all punctuations.

OR

Use the Regex Tool with options Case Insensitive, Parse, and Expression (\d+)

I hope you find this helpful - Cheers!

[rockeylearning · Accepted solution]

Thanks @RobertOdera for trying to help. I tried your method but it still left with the comma.

I'm not very good with regex, but I played around with this ToNumber(Replace([Input], ".", "")) and changed the data type a few times and was able to get it work now. Thanks.

[rockeylearning]

@PhilipMannering I was able to get it to work with this ToNumber(Replace([Input], ".", "")) with changing data type to different type.

[RobertOdera]

@Makes sense@@rockeylearning.

I'm glad we got got you close enough.

Thanks for your question and your feedback!