I have data in one column that must always be 8 numbers, leading zeros are
at least two and at most 5, I have formatted them with the custom number
format 00000### but when I import the file into Access (get external data) ,
Access will drop all leading zeros. I do not see an option to import a .prn
file into Access.

Hi

using a custom formal only changes the display of the numbers, not the
numbers themselves - which is why when you import them into access or
whatever, the numbers will lose the formatting.

the solution (AFAIK) is not to use a custom format but to change the numbers
to text with the leading zeros as part of the "number"
ie. say you have the number 100 in A1 and you want to see 00000100 instead
in B1 type
=REPT("0",8-LEN(A1))&A1

fill this formula down the column
then you can select this column, copy it and use edit / paste special values
to paste this over the original numbers and then delete this column.

Cheers
JuileD


"yesterdaytoday11" wrote in
message ...
I have data in one column that must always be 8 numbers, leading zeros are
at least two and at most 5, I have formatted them with the custom number
format 00000### but when I import the file into Access (get external data)
,
Access will drop all leading zeros. I do not see an option to import a
.prn
file into Access.

Are the leading zeroes literal digits, or are they added by your (custom)
formatting?

If literal, when you import to Access, I believe one of the last Wizard
steps allows you to click in the column and choose the Option for Text in the
upper right corner (new table). Otherwise the content is being inserted as a
number which does not allow for leading zeroes in Access. If being added to
an existing table the data must be put into a field defined as a text field.

HTH |:)

"yesterdaytoday11" wrote:

I have data in one column that must always be 8 numbers, leading zeros are
at least two and at most 5, I have formatted them with the custom number
format 00000### but when I import the file into Access (get external data) ,
Access will drop all leading zeros. I do not see an option to import a .prn
file into Access.

the zeroes are added by the custom format, the data is originally text but
must be converted to a number to allow formatting with leading zeroes.
Attempting to import as text into access,with the wizard, I only seem to get
the text option with the first field, the option is greyed out for any other
column or does not change as the right column is highlighted.

"CyberTaz" wrote:

Are the leading zeroes literal digits, or are they added by your (custom)
formatting?

If literal, when you import to Access, I believe one of the last Wizard
steps allows you to click in the column and choose the Option for Text in the
upper right corner (new table). Otherwise the content is being inserted as a
number which does not allow for leading zeroes in Access. If being added to
an existing table the data must be put into a field defined as a text field.

HTH |:)

"yesterdaytoday11" wrote:

I have data in one column that must always be 8 numbers, leading zeros are
at least two and at most 5, I have formatted them with the custom number
format 00000### but when I import the file into Access (get external data) ,
Access will drop all leading zeros. I do not see an option to import a .prn
file into Access.

the formula works great, thank you, I will copy and save it

"JulieD" wrote:

Hi

using a custom formal only changes the display of the numbers, not the
numbers themselves - which is why when you import them into access or
whatever, the numbers will lose the formatting.

the solution (AFAIK) is not to use a custom format but to change the numbers
to text with the leading zeros as part of the "number"
ie. say you have the number 100 in A1 and you want to see 00000100 instead
in B1 type
=REPT("0",8-LEN(A1))&A1

fill this formula down the column
then you can select this column, copy it and use edit / paste special values
to paste this over the original numbers and then delete this column.

Cheers
JuileD


"yesterdaytoday11" wrote in
message ...
I have data in one column that must always be 8 numbers, leading zeros are
at least two and at most 5, I have formatted them with the custom number
format 00000### but when I import the file into Access (get external data)
,
Access will drop all leading zeros. I do not see an option to import a
.prn
file into Access.

you're welcome and thanks for the feedback

"yesterdaytoday11" wrote in
message ...
the formula works great, thank you, I will copy and save it

"JulieD" wrote:

Hi

using a custom formal only changes the display of the numbers, not the
numbers themselves - which is why when you import them into access or
whatever, the numbers will lose the formatting.

the solution (AFAIK) is not to use a custom format but to change the
numbers
to text with the leading zeros as part of the "number"
ie. say you have the number 100 in A1 and you want to see 00000100
instead
in B1 type
=REPT("0",8-LEN(A1))&A1

fill this formula down the column
then you can select this column, copy it and use edit / paste special
values
to paste this over the original numbers and then delete this column.

Cheers
JuileD


"yesterdaytoday11" wrote in
message ...
I have data in one column that must always be 8 numbers, leading zeros
are
at least two and at most 5, I have formatted them with the custom
number
format 00000### but when I import the file into Access (get external
data)
,
Access will drop all leading zeros. I do not see an option to import a
.prn
file into Access.