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. (As an aside, not a single spam has been allowed through; why bother?)