Custom Format with leading zeros?

[sammipd]

I have a column of prices which need to be displayed in a four digit format without symbols. It also needs to keep the 4 digit format when used in a concatenate formula. $3.99, $5.00, $.47 should display as 0399, 0500, and 0047 and in the center of the formula as 1501x0399x1523. I either lose the leading zeros, or it automatically rounds up. How can I make this work? Thanks for your help!

 

[Rick Rothstein]

I have a column of prices which need to be displayed in a four digit format without symbols. It also needs to keep the 4 digit format when used in a concatenate formula. $3.99, $5.00, $.47 should display as 0399, 0500, and 0047 and in the center of the formula as 1501x0399x1523. I either lose the leading zeros, or it automatically rounds up. How can I make this work? Thanks for your help!

I don't think you will be able to format the values that way within the cells the numbers are in, but you can get them to appear the way you want inside of other formulas. For example, let's say you have these values in the indicated cells...

A1: $15.01
A2: $3.99
A3: $15.23

to get the display you show you want, you would need to use this formula...

=TEXT(100000*A1,"0000,")&"x"&TEXT(100000*A2,"0000,")&"x"&TEXT(100000*A3,"0000,")