Removing non-printable characters during an import

blackd77

Board Regular
Joined
Jun 17, 2004
Messages
88
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!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top