Use the text function in Excel to add leading zeros

Posted on:  04/04/2017
Use the text function in Excel to add leading zeros

When you enter zip codes in Excel, the leading zeros will get dropped. Here are some examples. Massachusetts starts with 01 and 02. Hew Hampshire is 03, Maine is 04, Vermont is 05, Connecticut 06, and New Jersey is 07 and 08. So when you type in the zip codes you will end up with a missing 0 at the start of the zip code. Microsoft Excel removes the leading zeros by default. That is frustrating when you are typing in product numbers, phone numbers, and other items that need leading zeros. This post is going to show you how to add leading zeros to numbers or text in ranges of cells in Excel.

The correct method to enter text in Excel

One way around this is before you start typing the zip codes, select the range you are going to use, and on the Home Tab, Number Group, click the drop down where it reads General (step 1) and select Text (step 2). Just so you know, you should use text for social security numbers, zip codes, product id numbers, and employee id numbers. Here is my rule. If you aren’t going to average, max, or use minimum functions, make it text. You would never average social security numbers.

Fix numbers that should be text in Excel

We are going to use the TEXT function in Excel. Now, if you pull in data into Excel or someone has already typed in zip codes, here is how you fix the numbers by adding the leading zero back. Click in a blank cell that is adjacent to the first number. As an example, see the image below. If E1 contains Zip and E2 to E9 have the actual zip codes, and your data stops in column G, you would click cell H2 and type =Text(E2,”00000″) and press Enter. The text function is saying make C2 text and use five characters. It will only put in the missing leading zero. It will not make C2 five zeros. After pressing Enter, AutoFill H2 to H9, copy, then click in E2 and Paste Values. Lastly, delete column H.

Example zip code

YouTube Video on the Text function in Excel adding leading zeros

Chris Menard

Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories