Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    data cleansing (2003)

    I am exporting data from excel to access. The data has internal workbook links containing ' characters and has peoples names as strings containing ' characters

    When I do a find and replace all ' characters on the sheet so as to cleanse the names before export, I also break links which I need to maintain. However I cannot find a method within "find and replace" which only replaces characters within values and leaves the formulas alone.

    The other way of approaching the problem is to cleanse the variables that hold the data while it is in transit between excel and access. However all the methods that I can think of which will cleanse these variables require a considerable amount of overhead.

    Can anybody assist?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: data cleansing (2003)

    How about making a copy of the workbook and replacing all formulas with their results (by using Edit | Paste Special with the Values option)? You could then replace apostrophes safely.
    If you need to do this frequently, you could create a macro to save the workbook under another name, remove the formulas and replace the apostrophes.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data cleansing (2003)

    Thanks. That will do it

Posting Permissions

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