import field with text and numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I see a lot of questions regarding this issue, however I have not found the
answer for my particular dilemma.

I have an Excel file with a field containing both numbers and text combined
with numbers (54847, 854C68 or HLXU5737). I do know when only numbers appear
in the first 25 fields or so, Access imports the field as number. This file
is updated and replaced with completely new data daily, and it would become
tedious to sort or change this prior to importing.

I am using the TransferSpreadsheet Action within a Macro to import this and
my other files for this Database.

Aside from manually sorting the field in exel, or making system wide changes
that I do not have access to do, how can I get Access to import the field as
text?
 
Any field coming from Excel using a mixture of alpha numerics, must be
formatted as a text field in Excel. If you enter a number into Excel it
assume the cell is numerical, as you so discovered. You must force Excel to
recognize the cell is text.
 
Most easily done by prefixing the cell contents with an apostrophe, e.g.
'54847

This doesn't show up in the worksheet or import into Access, but it does
ensure that the cell contents are treated as text in Excel and imported
into text fields in Access.

If you search at groups.google.com for apostrophe and my name you'll
find some Excel macros for adding and removing them.
 
Back
Top