Custom Format with leading zeros?

sammipd

Board Regular
Joined
Jun 6, 2010
Messages
61
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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,")
 

Forum statistics

Threads
1,081,623
Messages
5,360,083
Members
400,570
Latest member
Ben Morgan 1985

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top