Missing decimals in source data

Question

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

Valuewhat it should be53738537.3812804.00128.04841.12841.1284112841.12

tristank · Answer

Haha great idea @George_Fischetti

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

George_Fischetti · Answer

@tristank @cjaneczko @binu_acs - 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 · Answer

@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