Results 1 to 5 of 5

Removing non-printable characters during an import

This is a discussion on Removing non-printable characters during an import within the Excel Questions forums, part of the Question Forums category; Hello. I'm importing a text file that contains a carriage return in one of the fields. Excel sees this and ...

  1. #1
    Board Regular
    Join Date
    Jun 2004
    Posts
    88

    Default Removing non-printable characters during an import

    Hello. I'm importing a text file that contains a carriage return in one of the fields. Excel sees this and drops the content following that character to the next line, into column A. Running a routine like clean or substitute against the cell's contents doesn't replace the cells to where they belong (column E, in this case). How can I use Excel VBA to purge a string of this character before Excel sees it and does what it is told by the carriage return?

    I can do this with Perl, but I'd like to keep this in VBA, since the rest of the code that handles these files is in VBA.

    Thanks in advance!

  2. #2
    Board Regular
    Join Date
    Oct 2006
    Location
    日本
    Posts
    2,457

    Default Re: Removing non-printable characters during an import

    There are two carriage return characters:
    chr(10) and chr(13)

    When your code runs through, have it strip those characters to get rid of any line breaks.
    Download an HTML Maker to show your data, and please wrap all code in [code][/code] tags so we can read it. My mind-reading add-in is on my other computer.

  3. #3
    Board Regular
    Join Date
    Jun 2004
    Posts
    88

    Default Re: Removing non-printable characters during an import

    When I run code that removes this character from a range, it does not reposition the cells that were moved to the next line by the carriage return character. (At least it does not/has not done this for me).

    When searching the web for material relating to:

    VBA
    Excel
    replace or substitute

    What I would frequently find was a formula, without any code. I DID find this, which is what I have run:
    ------------------------
    Option Explicit
    Dim what As String
    Dim rep As String
    Dim myRange As Range

    Sub checklist_replace()
    Set myRange = Columns(4)
    rep = ""
    'rep = "Smith"
    what = Chr(10)
    'what = "Jones"
    find_and_replace
    End Sub

    Private Sub find_and_replace()
    myRange.Replace what:=what, Replacement:=rep, LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End Sub

    ------------------

    This runs okay, but does not reposition cell A5 (the destination of first "disrupted" content) into cell E4 (which is where the content would have been). If I switch the rep and what lines with the lines that are commented out below them, it performs the substitution perfectly.

    Is this a syntax error, or is the character correctly removed without a re-position of data?

  4. #4
    Board Regular
    Join Date
    Oct 2006
    Location
    日本
    Posts
    2,457

    Default Re: Removing non-printable characters during an import

    The code only has character 10, not character 13. Try checking for both at once. Also, what code are you using to import the data?
    Download an HTML Maker to show your data, and please wrap all code in [code][/code] tags so we can read it. My mind-reading add-in is on my other computer.

  5. #5
    Board Regular
    Join Date
    Jun 2004
    Posts
    88

    Default Re: Removing non-printable characters during an import

    Will do (scan for chr(13)). This character appears as chr(10) in a hex editor. That's why I chose that example.

    Import process: the original file is a .csv. I imported it as such. For this test, I didn't use code to import the data. I opened the text file with Excel, removed the bulk of the text, leaving a couple of good lines and two "bad" ones. Then I tried running the code shown. As noted, it worked well for a more straightforward replace, but didn't (appear to) do anything with the character replace.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com