Import Access into Excel has bad text format

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • I run huge Access queries for exporting data into Excel worksheets. When I then try to use Excel's Index function (to lookup 3 char text codes (i.e., "ABC") from "Access imported" worksheet column C to return text in column D (i.e., "alpha beta cigma") index fails with error #N/A.

    It seems that the imported Access text "ABC" in worksheet Data1 does not match my Excel entered text "ABC" in worksheet Data2. Only work around so far is to copy cell "ABC" from Data1 and paste over current Data2's "ABC" - rather exhaustive work for 64000 rows...then it recognizes it as text "ABC".

    There are no leading/following spaces, the column is sorted - it is simple "ABC" text match error.

    Tried to reformat column in Data1 to "text"... I've run out of ideas. If it is a reference error, not a visible one. I use the Index function successfully 99% of time...just not today. :?

  • There are actually 2 different kinds of printable blank spaces that can bugger
    up imported data from some DB applications.... I have come across something
    similar before... It was Aaron Blood who alerted me to the existence of the
    Char(160) & some code (EG Below) for sorting it out... this MAY be your
    problem & if so, the code will sort it out

    Normal functions such as TRIM & CLEAN will remove a

    Char 32 = Space

    but they won't deal with a more nasty hidden variety, i.e.

    Char 160 = No Break Space

    This may help... try it on a sample import...

    You'd just need to change this bit

    Set rngMydata = Sheets(1).[A1:A2000]

    to whatever your range is with your data..

  • Li response

    WillR, good advice for multi-word entries, but not relevant to my issue. My column text has format like "ABC" (no spaces, all entries are three character alpha codes). Issues is that Excel can't correlate the Access generated 'ABC' to the Excel generated 'ABC'... odd.

    Anyone else there have any suggestions?

  • Quote from liroberson

    WillR, good advice for multi-word entries, but not relevant to my issue

    How do you know ? Have you tried the code to see if it solves your problem - or have you just discounted it ?

    Believe me, whilst it MAY not be relevant or helpful... the Char(160) issue CAN be responsible for this issue - even with single LETTER imports, let alone whole words... not saying that this it IS your problem, but it's a common one... but how you can discount it without even running the code I'm not too sure :rolleyes:

  • Re: Import Access into Excel has bad text format

    I had a similar problem with dates. The cells in Excell looked like dates but were not because when I chagned the format of the cells to a different date format, the cells not change to the new format.

    But if I select the cell and click in it, the format will change when the cell lost focus.

    I ran the code above and sure enough, the correct format showed up.
    Then just to be sure I pasted the same dates in 5 columns, formated them all different, ran the trimmer(), and they all changed to the correct format

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!