Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import problem (Excel 2002)

    I exported a table from an Access database to Excel using the <font color=blue>Tools|Office Links|Analyze it with MS Excel</font color=blue> 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 <Enter>, 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 <font color=blue>File|Export</font color=blue> 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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Import problem (Excel 2002)

    You seem to be importing the "numbers" as text. Editing (F2) followed by <enter> changes it to a number.

    If you want to do all at once:
    Highlight the column, data - text-to-columns, delimited, <finish> should convert all at once.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import problem (Excel 2002)

    Some fixes are just too easy. Thanks, Steve!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •