Results 1 to 3 of 3

Thread: Irritating '

  1. #1
    Join Date
    Jul 2003

    Unanswered: Irritating '


    I'm working in an environment with lots of Access databases and Excelsheets.

    Sometimes we get a annoying ' at the beginning of a field when exporting/importing. WHY?

    Second question, how can I get rid of them? It's not a ordinary ' because I can't search and replace it...

    One method is to export it from Access as a tab separated file and import it to excel, then the 's are gone! But this only works with tables with fewer rows than 65536....

    WHY is this problem occuring and HOW can I fix it?

  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    One solution:

    SELECT AscW(Mid(columnA,1,1)) AS Expr1
    FROM a;

    Update a
    set columnA = 'newValue'
    where AscW(mid(columnA, 1, 1)) = targetNum
    Last edited by r123456; 11-12-04 at 10:59.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Chicago, IL
    The first answer is... That is the only way the tell Excel not to change your data. If you precede you data with the apostrophe Excel does not try to 'figure' out what kind of data it is. I work alot with zip codes and it is very annoying when you put in a zip code of 07827 and Excel automatically changes it to 7827 because it assumes it is a number. So entering '07827 will leave the leading zero on the front and not display the apostrophe.

    Second question... If you use the Mid function you can grab all but the first character in the string (in a query it might look like):


Posting Permissions

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