Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2003
    Posts
    69
    Thanks
    1
    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
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    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
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    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,"")
    or
    application.worksheetfunction.SUBSTITUTE(sImported Text,vblf,"")

    to use the vb constants instead of the chr function.

    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
  •