Removing non-printable characters during an import

[blackd77]

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!

 

[Sal Paradise]

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.

 

[blackd77]

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?

 

[Sal Paradise]

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?

 

[blackd77]

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.