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:
- Select the cells that will hold the data you are going to paste.
- Display the Home tab of the ribbon.
- Click the small icon at the lower-right corner of the Number group. Excel displays the Format Cells dialog box, with the Number tab selected. (See Figure 1.)
- In the Category list, choose Text.
- Click on OK.
Figure 1. The Number tab of the Format Cells dialog box.
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.
