On Thu, 3 Sep 2009 12:51:01 -0700, Richnet
wrote:

OK fine then. I'll try first to format the zip column in the spreadsheet to
zip+9 so everything is in the same format and the OLEDB provider won't get
confused. You would think that the zips that are 5 digit i.e. 32904 would
change to 32904-0000 right? Nope, Excel gives you 0003-2904. Brilliant.

I have no idea how your DB treats formatted Excel data. But if you want to
format cells that can have either 5 or 9 digit zip codes in Excel, and you are
getting the results you post, then you are not using the correct format option.

Formatting does not change the contents of the cell -- merely its appearance.

But if you want both 5 and 9 digit numbers to look like:
00000-0000

Then the format you should use is
Format/Cells/Number/Custom Type: [<100000]00000"-0000";00000-0000

or, if you want to turn the contents into a text string, you could set up a
helper column and use a formula like:

=TEXT(A1,"[<100000]00000""-0000"";00000-0000")


--ron