Liverpool Public Library

Expand Menu

Here's How You Can Create Custom Number Formats

by Keith Gatling | 4 years ago

OK, this one's gonna be incredibly geeky...but I just had to share it because some of you might find yourself running into the same problem.

I was entering data into a spreadsheet, and everything was going just fine until I got to the column for ZIP code. Some just weren't showing up right after I typed them in. 13088 showed up just fine. 13206 was fine too. 18018 was another good one, as was 29850. But 07017 and 02134 were problems. No matter what I typed, they kept showing up as 7017 and 2134.

I immediately knew what the problem was. Those ZIP codes had what we call "leading zeros." That means that they start off with zeros. But when you're dealing with numbers, a zero in the first position is considered an insignificant digit. What does that mean? It means it's a digit that can be ignored, because it doesn't make a difference numerically. That's why no matter what I did, those first zeros just wouldn't show up.

But I also had a hunch as to how to solve the problem. One was to fix the Zip code column so that it treated everything as that 07017 would be treated as a string of characters to be treated as text rather than as a number. Then it would show up exactly the way I wanted it to. But I seemed to recall that there was also a much better way, and after a little digging around, I found it.

I could create a custom number format.

What's that? It's just what it sounds like, it's a number format that you set up to your specifications to do exactly what you want, and show the numbers exactly the way you want them to show up. You can do these in Excel as well as Google Sheets, and they're useful for things like phone number formats, ID number formats, and many other things, including dealing with leading zeros.

How do you do it? Well, in the case of the leading zeros (which sounds like a mathematical murder mystery), in Google Sheets, after I've select the cells I want to apply this new format to, I go to Format/Number/More Formats/Custom Number Format, and instead of choosing one of their formats, type in 00000, and then hit Apply. This has now created a new number format that will always be five digits long, and will start with a zero if necessary. This means that if I enter 66, it'll show up as 00066. It also means that if I enter 02984, it'll now show up as 02984, and not just 2984.

Also, now, when you go to choose a number format, you'll be able to choose your custom formats from the list.

In Excel it's a little different. Here you go to Format/Cells/Custom, and then in the Type box, you'll enter 00000, just like you did in Google Sheets. The difference here is that your new format won't show up automatically in the list of formats to choose from from the menu. You'll always have to hunt for it by going through Format/Cells/Custom, and then looking through the choices there. Frankly, I think Google's way is a lot easier.

For a phone number, the format I'd create would be (000) 000-0000. Warning here...even though a lot of us are now using dots between sections of the phone number rather than dashes, don't do that here. Both spreadsheets will treat them as decimal points, and totally screw things up.

Well...I told you this was gonna be a bit geeky, and I hope I didn't disappoint you. I also hope this was useful to some of you.

And if you want to find out more, go to YouTube and look up "custom number formats."