Access Excel Linked Text and Number Issues

[Guest]

Question for Access and Excel XP (2002). I have linked an Excel Spreadsheet
(Database) to Access for updating and other special Access functions. The
Excel data contains a unique identifier (Key field) call Serial Numbers (SN)
that contains 13 characters. Some SN contain numbers only, while other SN’s
contain numbers and one letter i.e; 99923J143567. When linking the data
together Excel identifies the SN’d item as GENERAL, while Access pulls the
information in as TEXT. Access then adds a hash mark (‘) to the beginning of
each SN containing digits only (no letters) i.e; ‘99923J143467, thus making
the SN TEXT. This in turn causes problems when using the Vlookup function in
Excel, resulting in a no return value when entering a SN, unless the hash
mark (‘) has been included in the SN. If I remove the Hash Mark (‘) from the
Excel SN’s they will not show up in the Access linked database. I would
prefer not to separate this field if possible. Other then that I have tried
several methods and searched high in low in the MS Database for help with no
LUCK! Can anybody HELP me? Thank You!!!

PS: I Lost my previous post so I have reposted this... Sorry!

 

[tina]

please don't multi-post. if you need to post to more than one newsgroup
(rarely necessary), do it at the same time and list both newsgroup addresses
in the same post. this .externaldata group was a good choice to post your
question; there was no need to post the question in the .access newsgroup
less than an hour later. once you start a thread, recommend you wait at
least a day for a response before abandoning it and posting again -
especially on weekends, when newsgroup traffic is lighter.

 

[John Nurick]

Here's what I posted in your first thread:

If you have a text
column in Access there's no way to export a mix of text and numeric
values to Excel: Access uses the ' to force Excel to treat the values as
text. So the best thing is to adjust your Excel lookup formula to work
with text values. This works for me, using TEXT() to convert a numeric
value being looked up into text to match against the text values in the
lookup table:

=VLOOKUP(TEXT(A1,"0"),$C$1:$D$9,2,FALSE)

I suppose a purist might do

=VLOOKUP(IF(ISNUMBER(A1),TEXT(A1,"0"),A1),$C$1:$D$9,2,FALSE)