Bacon Bits:

No I'm not talking about the Bernie Madoff method of accounting. 

Side Note:  Does anyone else here thinks it hilarious that the phonetic pronunciation of Bernie Madoff's last name is 'made off"?  As in: "he made off with other people's money".  But I digress.

Some databases require a specific number of characters in certain fields. For example, a field called Transaction Number could require 10 characters. In these cases, a number is typically padded with zero. So transaction number 6546 would be entered into the database as 0000006546.

This is all fine and dandy, but if you're entering data in Excel, padding a number with zeros can be tedious. So here's a simple formatting trick you can use to automatically pad numbers.

Highlight the column you'll be entering your padded numbers, right click and select Format Cells.

This will open the Format Cells dialog box. Here, go to the Number tab and enter a set of zeros equaling the number of total characters you need for the field.

In this example, field I'm entering data for requires 10 characters. So I enter 10 zeros into the "Type:" field.

After you make this change, any number you enter into the formatted field will automatically be padded with enough zeros to get it to a minimum of 10 characters.  Plus, the formatting sticks with saving the file to Text/CSV. 

Bonus tip:  To achieve the same data entry functionality in Access, simply enter 0000000000 into the Format property of the field you are working in.

• Previous Entry: Dynamically Adding Primary Keys
• Next Entry: Bacon and Cheese Johnny-Cakes