We help IT Professionals succeed at work.

Creating a custom number format in Excel

Entering number in a column and want them to display as follows:
I created a custom format as follows:
But, most of the time, the last 3 or 4 digits are all zeroes, is 35600000.
Can I modify the custom format in such a way that any numbers not entered are recorded as zeroes.
In other words, if I enter just 356, can I get Excel to display it as 3560-00-00?
Watch Question

Top Expert 2016

You could use this format

to differentiate between whenever you have the first 4, the first 6 or all 8 digits


Bill GoldenExecutive Managing Member


It would appear this solution requires to enter at least 4, not 5, but 6, not 7, but 8 digits.  I am not sure my folks can get the hang of it.  Can we expand the format to provide for more ease on entering data?
I think you'll need VBA to "convert" the numbers. Try this code in the worksheets code module...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    'modify the range A1:A20 to match your input cells
    If Not Intersect(Target, [A1:A20]) Is Nothing Then
        Application.EnableEvents = False
        Target.Value = Replace(Left(Target.Value & Space(8), 8), " ", "0")
        Application.EnableEvents = True
    End If    
End Sub

Open in new window

Top Expert 2016

With formatting there is only 4 possibilities
Rob HensonFinance Analyst

Assuming 8 digits are entered using "0000-00-00" will work.

Maybe set Data Validation that the number entered has to be greater than or equal to 10000000

Rob H
Bill GoldenExecutive Managing Member


I "cobbled together" a solution.  98% of the time they enter 4, 5 or 6 numbers, so...


And, evidently, Excel 2003 only allows 3 possibilities.
To get to the worksheets code module to make use of my VBA solution, simply right-click the sheet tab and select "View code". Paste the code into that window, modify the range as desired then close the VB editor window.

Once done, any number entered into the specified cells will be appended with zeros to ensure they will always be 8 characters long, and standard formatting of 000-00-00 will work.

Be aware that solving this problem using formatting will negatively impact the ability to search (e.g. using VLOOKUP), aggregate (SUM, COUNT, etc), and sort on the values in the column in question as Excel will considered the number entered and not the number displayed.

For example if you enter 1234, 12340, and 123400 these will all be displayed the same but will not be displayed alongside each other when sorted if there are also values like 1235 and 12345 involved.
Bill GoldenExecutive Managing Member


Eureka!  It may be more exciting, or at least fulfilling, to learn something at 65 than it was at 25!  VBA will open up a whole new avenue of possibilities.  

I want to thank everyone who had a comment, but kudos to Wayne and Rgonzo1971.  Thanks to Rgonzo I now know a lot more about number formatting and Wayne has opened up the world of VBA for me.  It's all good and was time well spent!