We help IT Professionals succeed at work.

# Creating a custom number format in Excel

on
Entering number in a column and want them to display as follows:
1234-56-78
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?
Comment
Watch Question

## View Solutions Only

Top Expert 2016
Commented:
Hi,

You could use this format

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

[>9999999]####-##-##;[>99999]####-##"-00";####-"00-00"

Regards
Executive Managing Member

Commented:
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?
Commented:
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
``````
Top Expert 2016

Commented:
With formatting there is only 4 possibilities
Sorry
Finance Analyst

Commented:
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

Thanks
Rob H
Executive Managing Member

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

[>99999]####-##"-00";[>9999]####-#"0-00";####-"00-00"

And, evidently, Excel 2003 only allows 3 possibilities.

Commented:
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.

Commented:
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.
Executive Managing Member

Commented:
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!