Adding Types to Cell Formatting ?


Hi, I have been following the latest idiot proof user forms on the weekly You Tube tutorials, and on the second one which concerns Validation I have struck a problem. You demonstrate Cell Formatting regarding a Phone Number and click on the Format Cells > Number Tab > Special, your demonstration shows the Type page with 3 entries I believe a Post Code a Phone Number and something else. When I follow the same route, the Type Box is not populated. How do you populate it with the information that you want, in my case Phone Number and Postcode ? Help is appreciated, I am using MS Office 2016



Selected Answer

Don, who wrote the tutorial, may have a better answer, but on my laptop, installed with Excel 2013, the only locale where the Type field isn't blank is English (United States). So, the obvious answer should be to change the Locale (location). You will find it below the Type field.

Of course, that will (may) upset other aspects of Excel, such as your date formats. Therefore the better solution may be to look behind the scenes.

When you select one of the Special formats Excel actually creates a Custom format, and it is that Custom format which is applied to the cell. It will appear in the list of Custom formats although you didn't put it there. Excel did. It might well be that this reaction is different depending upon which language and locale is your default but the upshot of it all is that you don't need the Special formats, and most certainly not if your installation of Excel offers no special formats in the first place. You can create your own Custom formats for zip codes and telephone numbers to display them in the manner in which they should be displayed in your locale (country).

You might google for "Excel custom cell formatting" and you will find plenty of help. You might also ask a specific question here (please make it a new question, though). The format below is the Custom format my Excel created when I used the Special format for United States telephone numbers.

[<=9999999]###-####;(###) ###-####

This instruction includes two parts, separated by a semi-colon.

  1. ###-####
  2. (###) ###-####

If the number entered in the cell is <=9999999 the first format will be used, for larger numbers the second. The implication is that there are no telephone numbers with more than 7 digits. If a number has more than 7 digits the extra leading digits will be deemed to be an area code and placed between parentheses. It follows that this system can't display international numbers, nor can it display area codes starting with zero. It can also not display area codes correctly if a number has less than 7 digits. In other words it is only conditionally useful in the US and quite useless in most other countries. That, of course, explains why Excel doesn't offer it for other countries.

However, the reason I explain this is to show which rules applying to the numbers must be known before an intelligent custom format for them can be created. Custom formats can be created not only for dates, zip codes or telephone numbers but also for part numbers, insurance policy numbers etc, in fact for any kind of number which has a fixed format, including alphanumeric numbers.



Hello. Thanks for the answer unfortunately I am in the UK and as you corretly state Excel does not offer these options for users outside the US. Thanks anyway. 
Jim W (rep: 14) Nov 11, '18 at 9:23 am
At this link you will find instructions for creating a custom number format for UK telephone numbers. The difference between "Custom" and "Special" is merely that the Special one is identifiable by name.
Variatus (rep: 4108) Nov 11, '18 at 12:44 pm
Thanks Variatus thats is spot on. All sortred now.
Jim W (rep: 14) Nov 13, '18 at 9:56 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login