Access Excel Linked Text and Number Issues

[Scott]

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!!!

[Debra Dalgleish]

You could try converting the number to text in the VLookup formula. For
example, with a number in J2:

=VLOOKUP(TEXT(J2,"0"),$A$1:$H$500,2,FALSE)

Scott wrote:
> 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!!!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[Scott]

Hi Debra,

Thanks for the quick response. If I'd written my request a little clearer
you'd have be able to understand it better! After I wrote my question, I
even questioned what I wrote? Sorry! I think the fix you have posted will
help me but my next dilemma is getting the formula to work with my needs
i.e.; Type in a Serial Number (text or numerical value) and return a
specified value from an Excel spreadsheet or Access Database. Right now the
Excel formula I've been using is as follows:

=IF(ISERROR(VLOOKUP(A1,Excel/Access!$B$1:$C$100,2,FASLE)),"",VLOOKUP(A1,Excel/Acess!$b$1:$c$100,2,FALSE))

I also posted this question on the Access side of the discussion group and
a man by the name of John, came up with the same solution/formula as yours.
He is looking into it "I Think"? I'm not sure where to add the TEXT value?
I've tried to make the formula work to no avail. Unfortunately, I know very
little about programming excel formulas when it comes to multiple functions
"OUCH"! I don't want to waste your time If somebody else is working on a
solution. I truly appreciate your feedback, and would like to Thank You Very
Much for your response... Scott...


"Debra Dalgleish" wrote:

> You could try converting the number to text in the VLookup formula. For
> example, with a number in J2:
>
> =VLOOKUP(TEXT(J2,"0"),$A$1:$H$500,2,FALSE)
>
> Scott wrote:
> > 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!!!
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>