I exported a table from an Access database to Excel using the Tools|Office Links|Analyze it with MS Excel method. One of the fields is exhibiting some rather odd behavior. It is a number field filled with 12 digit numbers. The first digit of the field is a leading zero.
Well, I exported this to Excel in order to add another column to the data using the Vlookup function. The lookup column is this 12 digit number field. The matching value in the lookup table array, however, is only an 11 digit number–without the leading zero. This fix seems simple enough to accomplish with the RIGHT function, but Vlookup returns #N/A. Interestingly, if I select one of the cells in lookup column and press F2 and them , the leading zero goes away by itself and the Vlookup function works properly. Great.
Here’s my problem. I have well over 50,000 records in this database. I cannot enter edit mode for each row, and I tried to select a range of cells, but (obviously) cannot enter edit mode for more than one cell at a time. Does anybody know exactly what change is being made by just entering edit mode, and is there a way to affect this change to multiple cells?
I tried exporting the data using the File|Export method, but this presented another set of challenges. I tried just copying the column to an adjacent column, but that didn’t affect the leading zero. I tried using the RIGHT function, but that didn’t work either, even though the content of the cell looked correct.
Thanks for any help you’re able to offer.
