Won't Import my Numbers Correctly

[tanyalee123]

Good morning!


I have a simple Excel spreadsheet with 2 columns - 1st is a number, 2nd is Text. I need to update an existing table with this new info. The plan is to create a table with my Excel data and then do a query to update the existing table.

So, the problem is that in the excel spreadsheet there are hundreds of number that are 3 and 4 digits - these are medical codes and for our purposes they need to be 5 digits. So I formatted the column using CUSTOM formatting and added 1 or 2 zeros to the beginning of the numbers. That worked fine. However, when I try to import it (I used copy and paste; that seemed the simplest way to do it) it deletes the zero/s that I put there. When creating my table in Access to import the data, I set field to Text and field size to 5, however, I believe doing that simply tells the table that 5 is the maximum, but doesn't indicate it "must" be 5. (By the way, I tried setting the field to Number, and that didn't work).

Does anyone know how I can get Access to read these zeros....or another way that I can get this accomplished (like something I can do after the import to get the zeros in front)? Thank you!

Tanya

[JoeM]

If you applied a Custom Format in Excel, the values are still really numbers, and the leading zeroes really are not there. It is just formatted to show it looking like that (to see what is really in an Excel cell, simply click on the cell and look in the formula bar).

As such, when importing them into Access, you will not see the leading zeroes because they aren't really there. You could import them as numbers, and apply the same Custom Format in Access as you do in Excel to show leading zeroes (but once again, they aren't really there).

If you want to make them text values with leading zeroes, use the Text function, i.e.
=TEXT(A1,"00000")
Then import that column.

[tanyalee123]

Thank you very much! This worked perfectly!