Pasting Leading Zeroes

Some data requires leading zeroes. The first example that comes to mind is ZIP Codes, in which some have leading zeroes. There are other examples, as well. For instance, you may have a chart of accounts in which general ledger account numbers start with leading zeroes.

When you paste information into Excel, it normally tries to "parse" the data and put it in a format that it can work with. When you paste data that have leading zeroes, and the data could reasonably be construed as numbers, then Excel strips the leading zeroes from what you are pasting. For instance, 0012387 become a number value, 12387.

What if you want to retain the leading zeroes? All you need to do is make sure that the target cells—the ones that will receive the data being pasted—are formatted as text. Follow these steps:

1. Select the cells that will hold the data you are going to paste.
2. Display the Format Cells dialog box. (In Excel 2007 display the Home tab of the ribbon and click the small icon at the lower-right corner of the Number group. In older versions of Excel choose Cells from the Format menu.)
3. Make sure the Number tab is selected. (Click here to see a related figure.)
4. In the Category list, choose Text.
5. Click on OK.

Whatever you now paste into the formatted cells is assumed to be text, and Excel will leave your leading zeroes exactly as you expect them.