Creating a custom number format in Excel

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?
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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
Bill GoldenExecutive Managing MemberAuthor 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?
Wayne Taylor (webtubbs)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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Rgonzo1971Commented:
With formatting there is only 4 possibilities
Sorry
Rob HensonFinance AnalystCommented:
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
Bill GoldenExecutive Managing MemberAuthor 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.
Wayne Taylor (webtubbs)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.
scsymeCommented:
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 MemberAuthor 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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.