Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2003
    Thanked 0 Times in 0 Posts

    External Data Format (2000)

    Hello, I have created a spreadsheet that contains a query that imports data from an external source (using ODBC). This external source is an accounts payable software. One field in the data entry screen is a notes section which is similar to a notepad. The query in my spreadsheet can pull the exact data that is entered in this notes section, but the returns at the end of each line have a rectangle at the end of each line. This rectangle is not a character in the character map, so a find and replace will not work to delete these rectangles all at once. (The notes can be several lines long).

    Is there a way to delete these rectangles all at once or prevent them from appearing in the first place?

    Thank you in advance for your help and advise.

    -tmg9671 <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Louisville, Kentucky, USA
    Thanked 0 Times in 0 Posts

    Re: External Data Format (2000)

    You could try the CLEAN function.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: External Data Format (2000)

    If you can find the ascii code number you can use something like
    application.worksheetfunction.SUBSTITUTE(sImported Text,chr(xxx),"")

    to replace all occurences with where:
    sImportedText is the variable name holding the the imported text
    xxx is the ascii code for the character

    To find the code. If it is the last character in the string:
    xxx = asc(right(sImportedText,1))

    I would guess it is either chr(13) which is a "carriage return" or chr(10) which is a line-feed. So you could use:
    application.worksheetfunction.SUBSTITUTE(sImported Text,vbcr,"")
    application.worksheetfunction.SUBSTITUTE(sImported Text,vblf,"")

    to use the vb constants instead of the chr function.


Posting Permissions

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