Text format for numbers to preserve trailing zeros

[spectraflame]

I have a column of numeric values (####.##) that I need to convert to text. However when it is converted to text the trailing zeros after the decimal also need to be retained. These digits are going to be combined with other text fields in a single text string to create a TXT file to then upload.

So 4681.98 would be 4681.98
and 4326.20 would be 4326.20

I have tried using a custom format of 0000.00 however when I attempt to join that with the other values, the trailing zeros are dropped.

The end result also must be 10 characters in length. The value must be left justified with the extra spaces after the last decimal place. I have tried using =LEFT(C1&REPT(" ",10),10) however when trying it with the value of 4326.20 the result is 4326.2

Any suggestions on what I am missing?

 

[NeonRedSharpie]

The magical if statement. Basically just see if it needs an ending zero. If it does, add one in.

=IF(LEN(RIGHT(C1,LEN(C1)-FIND(".",C1)))<3,LEFT(C1&0&REPT(" ",10),10),LEFT(C1&REPT(" ",10),10))

 

[spectraflame]

How about:

=IF(LEN(RIGHT(C1,LEN(C1)-FIND(".",C1)))<2,LEFT(C1&0&REPT(" ",10),10),LEFT(C1&REPT(" ",10),10))

The <3 would add a 0 at the end???

 

[spectraflame]

When I use the original formula you posted on a value like 4136.00, I get #VALUE!

 

[NeonRedSharpie]

When I use the original formula you posted on a value like 4136.00, I get #VALUE!

That's an easy enough fix. My formula assumed that all values had a decimal. So, in the case that they don't.....

=IFERROR(IF(LEN(RIGHT(C1,LEN(C1)-FIND(".",C1)))<3,LEFT(C1&0&REPT(" ",10),10),LEFT(C1&REPT(" ",10),10)),LEFT(C1&".00"&REPT(" ",10),10))