Control phone number and postal code formats using built-in options or customized formats

- [Narrator] In this worksheet we see four different kinds of phone numbers as commonly used within the United States. Now in all these cases here in cells A2 over to D2 the characters as we see them have all been typed so in the last example, in D2, what was actually typed was three zero three period four four zero period and the other numbers. Over in column A, the parentheses, the space, the dash have been typed, and so on. Now we can in each of these cases either by using a built in format or creating a custom format make data entry faster and more reliable, less error-prone, by taking advantage of some of these built in features.

So if we like the format in column A let's apply the format for all cells so that we, in the future here, in these other entries, will need only type the actual numbers. So selecting column A we can do this quickly by right clicking column A, and, at the same time, choosing format cells. Within the format cells dialog box choose the number tab if it's not already selected and then under special we do see phone number. There's no preview here by the way to give us a hint as to what we're about to do but once you see this you'll use it.

Click okay. Now what does that mean? In cell A3, A4 as we put in phone numbers later or maybe the same one just for the moment here three zero three four four zero one three eight zero, I'm just typing the numbers, press enter, and we see the format. Here's another one. Two one two three four five six seven eight nine, we're just typing the numbers. And it's faster and more reliable. Now there's no built in format for what we see in columns B, C, and D, but we can do the same sort of thing here by way of what's called a custom format.

I'm going to select column B. Another quick way to get into format cells is if the column is already selected, press Ctrl + 1. Now this time under format cells, number tab, we want to choose custom. And in the panel here right under the word type we're likely to see general, let's highlight that, and instead type in a format. Now if you've never used this feature before you wouldn't necessarily know that the zero character stands for a numeric position character so if I put in three zeroes here that represents the area code, those are always numbers.

So I want to have three zeros, and then, within double quotes, double quote slash double quote, I will display this format larger in a moment. So three zeros and then a slash but it must be embedded within the double quote characters, one on each side, and now three more zeros to represent the next three numbers a dash, we don't need to put this within double quotes, so simple a dash, and then four zeroes. Now I'm going to highlight this, and copy it, Ctrl + C, we'll see that in a moment, I'll paste it on the screen.

Click okay. First of all what's happened here? I'll type a number three zero three, four four zero, one three eight zero, as I press enter we see the format like that. And of course with other numbers as well. Two one two, three four five, six seven eight nine. The format as it's being displayed in column B is as we see it here. That's how it needs to be set up as a format. You could guess what we're about to do in column C, almost the same thing, this time I'll right click column C, format cells, custom, here under the word type.

In the panel it says general, three zeros a dash, three zeroes, a dash, four zeroes. So for the slash we needed in the previous example to put it within double quotes for the dash we don't need to do that so there's the format I'll display this too momentarily on the screen with Ctrl + C here. Apply the format by clicking okay I'll paste the format right here that's the format for column C. And once again type in that same number.

And so on. And similar here and you probably would know what to do here column D, I'm going to right-click, go into format cells, and here too. Custom, and here under type, general, here. Zero zero zero double quote period double quote zero zero zero double quote period double quote and four zeroes. And here too I'll copy this. Click okay and the format here is as we see it.

So I'll type the number here and as you would guess that's what we see. These columns I used a custom format the built in format for column A as we saw earlier. Now for zip codes sometimes you forget if you don't live on the east coast New England zip codes begin with zero, some in New York State do, in fact, some in New York State begin with a double zero. So if you got a bunch of codes here you're not thinking much about it there's a Massachusetts zip code you type zero five six seven eight something like that maybe, you press enter, and it looks like this and nothing horribly wrong but you would want to see the leading zero there.

Now I typed the zero there but it doesn't display. Let's format this with zip code this is similar to that first phone example so I'll right click column F go to format cells, special, zip code, notice there is an option for zip code plus four if you use that, zip code, click okay. So now type a zip code, if it is one of those New England zip codes that begins with zero, I will not type the zero, don't need to, what if it's zero four five six seven? I'll type four five six seven, enter, so on.

It doesn't prevent you from putting in six characters which would be wrong, no correction there but, any those leading zeros, and if it were one those unusual entries that begins with two zeroes what if it were zero zero one two three? I'll simply type the one two three. Social security numbers, often they are typed with dashes but you don't need to type them. Here too, right clicking column H, going into format cells and on the number tab, under special, there is social security number.

Click okay so as we type a social security number here four five six zero zero three four seven eight I'll press enter. I've only typed the nine characters but the dashes appear automatically. By the way a source of confusion and a source of some real problems sometimes in Excel is when you're trying to match up two different lists maybe by way of social security number and in one list the actual dashes are there they've been typed, in the other list, maybe they haven't been, but the format's been applied, you will not get a match so you want to make sure that those two entries are similar and I would strongly advise that if you are using social security numbers if the dashes are actually there get rid of them, you could do it quickly, by going to find and select and you could replace the actual dashes with nothing and you do a replace all for an entire column then apply the format and then entries would look like this but if you're making comparisons with other lists as long as they're in sync and you have the same format applied in each case it's going to work okay for those matches.

So prior to this we saw how to make zip codes be standardized too with the built in formats we saw that earlier example of a phone format for column A and then the custom formats that were applied in columns B, C, and D to ease data entry and eliminate a lot of typos.