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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
With formatting there is only 4 possibilities
Sorry
Sorry
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
Maybe set Data Validation that the number entered has to be greater than or equal to 10000000
Thanks
Rob H
ASKER
I "cobbled together" a solution. 98% of the time they enter 4, 5 or 6 numbers, so...
[>99999]####-##"-00";[>999
And, evidently, Excel 2003 only allows 3 possibilities.
[>99999]####-##"-00";[>999
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.
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.
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.
ASKER
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!
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!
ASKER