I have a project where I need to use part of a column of numbers as a "Key" when I import Excel into Access. The last 19 numbers are associated with an account number. The account number is going to be the "Key" in Access.
The issue is that the column is currently comprised of 19 characters (numbers), a dash, followed by 19 more numbers. As long as I leave all 29 characters in the column, I can force Excel to format the column as Text (instead of a number) and tell Excel to ignore the error.
However, I want to parse the column and have a column containing ONLY the last 19 numbers.
The second I split the column, Excel turns the numbers into a scientific equation. If I add an apostrophe after-the-fact, Excel does turn the number back into a number, but it converts the last three digits to zeros instead of the real numbers.
I also tried insert apostrophes BEFORE I split the column but that did not work.
Access does not allow me to split the column as part of the import process. And I have too many files to manually type the file names in every one.
I've attached a small example and I'm hoping that someone has an idea.
Bookmarks