Keep/Bring Back Trailing Zeros

[ktschultz92]

Hello All!

I have a report that I dump from a vendor online. The only option I get is to download it as a CSV file. Sometimes, some of the order information has trailing zeros. The CSV file automatically gets rid of them, but I need the trailing zeros back. This column of order information is referenced in another sheet of a master worksheet. The order information can be in one of two formats:

98765.4321
98.7654.321

It has been my experience that the latter of the two does not drop off zeros, as the second period converts that item to text. However, if I have 98765.4320, it is automatically shown in the CSV download as 98765.432. This happens often enough, and with new orders, too.

I have tried the ROUND formula... that doesn't seem to have worked.

Thank you for the help!

 

[Max1616]

If the problem numbers always have 4 values after the "." then you can use this formula (assuming your values are in column A):
=TEXT(IF(LEN(A2)-2 = LEN(SUBSTITUTE(A2,".","")),A2,IF(LEN(MID(A2,SEARCH(".",A2)+1,100))<4,CONCATENATE(A2,INDEX({"0","00","000","0000"},4-LEN(MID(A2,SEARCH(".",A2)+1,100)))),A2)),"#.0000")

This will add zeros if multiple are missing from the end of the problem pattern.

Hope this helps.