Wednesday, November 18, 2009

Excel - Import Leading Zeroes

HowTo: Import numbers into Excel without stripping, truncating or loosing leading zeros. A classic example are zipcodes being imported from an external source. Codes, such as "08401" can import as "8401".

When Excel imports numbers, it assumes the "General" format for each column. Fields, such as part numbers, zipcodes, SSN, and other non-numeric numbers will have leading zeroes stripped because Excel thinks they are real numbers. Prevent this by forcing the import as TEXT.

In order to do this properly, you must run Excel's Import Wizard (steps 1 through 3). For this article, assume a simple tab-delimited data file with City names, State codes, and ZipCodes (file columns may not line up this pretty in your actual data file):

Launch Excel and start at a blank sheet

1. Select File,Open: Tunnel to your test data file. Be sure the file extension is .txt, .tab, or .csv in order to trigger the Import Wizard

2. On the 'Text Import Wizard', step 1 of 3, choose "Delimited"

3. On step 2 of 3, choose ([x] Tab)

4. Here is the key to the problem:

On step 3 of 3, click the first column, then Shift-Click the last column (highlighting all columns). Select Option "Text" and complete the import. (Most files should import all columns as text. Optionally, you could select only the ZipCode column.)

Results in Excel will look like this:

Related Keyliner Articles:

Excel UDF (User Defined Functions)
Using Excel for Raffle-Ticket Drawing: Prizeorama
Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
VB - Return First Word, Last Word, Supertrim
Using VBA to Send Email
Using Excel to select Raffle Tickets - Prize-orama

No comments:

Post a Comment

Comments are moderated and published upon review.