Missing decimals in source data

[George_Fischetti]

Hi folks, I need some help formatting a field in my source data that has some inconsistent values.  This field represent the unit cost of a product/service and should be in the typical  x,xxx.xx format, but while testing the data I've found some values that are missing the .xx OR applying it incorrectly.   I'm not great with regex but have a feeling there could be a method to search for these errors and fix them.   a Sample of my data is below, hoping someone can help.  Thanks, George

Value	what it should be
53738	537.38
12804.00	128.04
841.12	841.12
84112	841.12

[binuacs]

@George_Fischetti I assume you need your data in xxx.xx format, in that case the below formula should work

[cjaneczko]

Another approach.

if ToNumber(Right(ToString([Value],2), 3))>0 then [Value] else [Value]*.01 endif

[tristank]

@George_Fischetti I think binuacs addressed this well but I thought this was a fun challenge and decided to throw my own little solution in.

My formula assumes that the last two digits will be decimals. If there is already a decimal then nothing happens but if there is one then a decimal is placed before the last two digits, no matter the size of the value. The only caveat here is that the values have to be strings in the formula tool but can then can be converted back into doubles.

Cheers,

Tristan

Formula:

IF Contains([Value], '.')
THEN [Value]
ELSE
Left([Value], (Length([Value])-2))
+ "." + Right([Value], 2)
ENDIF

[George_Fischetti]

@tristank @cjaneczko @binuacs - thank you all for your input, all of these suggestions worked. I'm also in contact with the owners of the source data in case there is a data issue, which I do suspect.   I wanted to have a work around in place in case the data remains as is.   Powerful uses of the IF and IIF statements, appreciate the learning experience.

Thanks again!

[tristank]

Haha great idea @George_Fischetti 

Better to fix the problem at its source then do some long-winded workaround