Leading zeros lost importing from Excel

  • Thread starter Thread starter SueD
  • Start date Start date
S

SueD

I have a worksheet containing a column of part numbers
that are 7 digits. The first 2 in most cases are 0. I can
force the display of the zeros in Excel by using a custom
number format but when I import the worksheet into an
Access table it drops the opening zeros. I can add formats
to the Access table to show leading zeros but I need the
field to actually be 7 characters with leading zeros. Is
there a way of forcing 7 charcters to be imported or to
run a check against the part number field and add missing
characters to the front. In the Access table the Part
Number field is text.
 
SueD said:
I have a worksheet containing a column of part numbers
that are 7 digits. The first 2 in most cases are 0. I can
force the display of the zeros in Excel by using a custom
number format but when I import the worksheet into an
Access table it drops the opening zeros. I can add formats
to the Access table to show leading zeros but I need the
field to actually be 7 characters with leading zeros. Is
there a way of forcing 7 charcters to be imported or to
run a check against the part number field and add missing
characters to the front. In the Access table the Part
Number field is text.
Click to expand...
Access and Excel have a different mind when they look at columns and try to
determine if they are numeric or text. In the Excel spreadsheet, put an
Apostrophe
( ' ) in front of the data in the Excel spreadsheet. Sometimes you only
need to do this
for the first 5 or so rows. Then during the import Access will see the
column as text.

Ron
 
If you chose text format for the cell you can have the zeros in Excel but if you want to treat the cell as a number you cannot add zero by custom format because it does not take it as part of the number. When you want to trasfer to Access you should first make a table and set up the length of each field just similar to the Excel spread sheet . Then you can copy and paste whole table.
 
Back
Top