Unanswered: SQL Server WILL NOT import properly from Excel!!
ARRGGH, I'm going to kill Excel or SQL Server!! I've spent an hour on this and I can't get it working. I'm importing some address data, and several of the zip codes are importing as NULL, even though in the Excel sheet, they look fine! Excel is complaining, something about the zip codes are numbers entered as text..whatever.. so I edited my SQL import and made it so the ZIP field gets created as varchar 50 instead of float. It still won't import right.
Your zip column is probably a mix of numeric and text cells. DTS doesn't handle that very well. Force all of the cells to be one thing or another, then do an import based on whichever datatype you picked. That should get it working fairly smoothly.
I think you are changing the formatting, not the datatype of the cells.
Insert an empty column to the right of the zipcode column. In the cell in the empty column next to the first zipcode, put the formula
Replicate the formula for all of the zipcodes, then Copy and paste Special Value the new column you've created (effectively replacing the formulas with their values). At that point, you can delete the original column.