Using Leading Zeros in Numbers

If you wish to represent a number in a cell with leading zeros, such as a zip code that starts with a 0 digit, or an International phone number, then you can do it using the Text cell format which treats the number as a series of characters, rather than a value. You can also use custom cell formatting to display a number with leading zeros, which can help you if you wish to have easy data entry and uniform cell formatting.

You can also watch this video at YouTube (but with ads).

Video Transcript: Let's say you're entering some data into Numbers and the data happens to be something like zip codes. You enter one zip code, another zip code, and then you enter a zip code that happens to start with a zero. Notice how it cuts the zero off the beginning. So how do you get leading zeros in Numbers?
Well, the problem here is the data type. If I click here on the entries and I can see the actual values here at the bottom. Of course entering 02108 is going to simply give it the number 2108. Now with a zip code that starts with a non-zero digit it looks the same. But clearly that's not what we've got here. We're not using real numbers that we will be adding and subtracting and things. We're going to be using, basically, a series of characters to represent something like a location. So how do we change this?
Well, there's several different ways to handle it. One good way to deal with it is to change the data type. So I'm going to select column B here and I'm going to go to Cell. Here in the sidebar you click Format first and then Cell. Then I'm going to change the data format to something that will better represent this type of data which in this case would be Text, not a number. So now I can erase this one here and when I enter in a number starting with zero it will accept it because it's actually taking this as a piece of text. If I look at the bottom here I can see it says Text and then each value. So that's one way to handle it. Simply treat these as text rather than numbers. 
So another way to deal with it would be to create a custom data format. So let's Undo here until we get back to where we just had these numbers and we have the one here that doesn't work really well, the 2108. Let's select the column and let's change the Format to Create Custom Format. Now this is where we can create something that will better represent the type of data we want. So in this case it's going to be five digit zip codes. So we'll change it to Zip Codes, just call it that so we can refer to it later, and we'll set it as Type, Number. Then here we can basically create what we want. 
So instead of selecting what's here, this little default thing, I'm going to click the little arrow here and I'm going to Add Digit so it's five digits there. Then I'm going to Hide Separator, to get rid of the comma, and I'm going to select Show Zeros for Unused Digits. Hit Okay. Now you can see that number changes there to include that leading zero. So I can now enter in 999 and it's going to be 00999. So that works out really well. What happens if I enter in six digits? Obviously for a zip code you wouldn't want to do that but it would work just fine. It's just going to make sure that there's at least five digits there using this Custom Format that I've created. You can play around with other types of custom formats you can create in here as well and change how things work. You can see here you can actually set the number of digits. So here I've set it to five. 
Now another type of number that sometimes has a leading zero is a phone number. Especially international phone numbers. So here we've got an international phone number and it starts with zero but if we enter it in here and hit Return the zero is dropped. So an easy way to deal with that would be to change the format to text and then I can put the zero there and it will stay there. In fact I can use any formatting I want. So if I want to put, say, a space and then parentheses around the area code and then a dash, you know, I can. But you can also create a custom format for this as well.
So I have this set to a number now. I can go in and say Create Custom Format and I'll call this one International Phone Number. I will then manipulate these quite a bit here in custom format. I'll take the default one here and I will change it to number of digits, 3, and I'll set it to Show Zeros for Unused digits. Then I will a space after it for regular characters, ones that aren't going to be replaced by numbers. You can just type them. So I'll type a Space and a left parenthesis and then I will drag another integer in there and I will set that also to 3. I will also set that to Show Zeros just in case. A right parenthesis, a space, let's drag another integer in here and let's set this to 3 digits and Show Zeros. Then I'm going to put a dash and finally 4 characters there but I'm going to make sure this separator is invisible and Show Zeros. So that's the format I want. I hit Okay and you can see now it nicely sets the format there. 
So if I set that for the entire column, I'll click on the column there. I'll change the format to the International Phone Number format I just created. Now the cool thing is I can just type and hit Return and it formats like that. So now I can only type digits and everything will be formatted the same way.
So some handy ways to deal with leading zeros using Cell formatting in Numbers.

Using Leading Zeros in Numbers

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information