link with excel returns #NUM errors

[Guest]

I have a files that I download every morning with prices. I've read that if
text & numbers are combined in a column, the #NUM error is returned. THe
file is 7000 records long from excel and changes daily, so it's not possible
to type an apostrophe in each of the cells at fault. However, I do need the
info from these columns. Any ideas?

 

[Joseph Meehan]

I have a files that I download every morning with prices. I've read
that if text & numbers are combined in a column, the #NUM error is
returned. THe file is 7000 records long from excel and changes
daily, so it's not possible to type an apostrophe in each of the
cells at fault. However, I do need the info from these columns. Any
ideas?

I would start by importing it as a text field. Then I would take a look
to see exactly what the problem was and form a plan of action from there.

I believe you could play with a query to find and/or change data as
needed using something to identify any filed not exclusively numbers and add
the apostrophe or clear it or whatever works for your needs.

 

[Van T. Dinh]

I find the easy way is to modify the Excel file as follows:

* Insert a "calculated" Column next to the MixedColumn using the function
TEXT() as follows:

= TEXT([CorrespondingCellInMixedColumn], "General")

for all cells in this Column. Save and close the Excel file.

* Link the Excel file to Access, ignore the original MixedColumn and use the
"calculated" Column. All values in this Column will be Text so the values
won't have #NUM entries.