I have seen many posts about removing character, but I have a very
meesy spreadsheet that I want to do a remove on. I can't tell which
field the char is in.

" 2009 D 5/15/2009 W Postwar auto racing

Lots of char that look like enter - how can I easily remove them from
an entire txt file?

Don't bring them into a worksheet it will make the problem worse.
either manuall go to Notepad and replace the bad characters or write VBA
code that opens a file and fixes the the problem in the text file and
not in an excel worksheet.

Below is a program I wrote which fixes the CR and LF in any text file
to make it compatible with windows. I use to move text files between a
PC and a Unix work station and code like this was very helpful. I don't
know what the exact problems are with your files so I'm not sure if this
will help.

sub FixEOL()

Const ForReading = 1, ForWriting = -2, _
ForAppending = 3


CR = Chr(13)
LF = Chr(10)

ReadFile = Application _
GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select Read File")
If ReadFile = False Then
MsgBox ("No file Selected - Exiting Macro")
End If

WriteFile = Application _
GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select Write File")
If WriteFile = False Then
MsgBox ("No file Selected - Exiting Macro")
End If

Set fs = CreateObject("Scripting.FileSystemObject")
Set fin = fs.OpenTextFile(ReadFile, _
ForReading, TristateFalse)
Set fout = fs.CreateTextFile _
(Filename:=WriteFile, overwrite:=True)

FoundCR = False
Do While fin.AtEndOfStream < True
ReadData = fin.read(1)
Select Case ReadData

Case CR:
If FoundCR = True Then
'two CR in a row write LF inbeteen the two CR
fout.write LF
fout.write CR
Else
FoundCR = True
fout.write CR
End If
Case LF:
If FoundCR = True Then
''Normal sequence CR foloowed by LF
fout.write LF
FoundCR = False
Else
'Bad Sequence LF without CR, Write CR
fout.write CR
fout.write LF
End If
Case Else
If FoundCR = True Then
'Bad Sequence CR without LF, wite LF
fout.write LF
fout.write ReadData
FoundCR = False
Else
'Normal dequence of two character in middle of line
fout.write ReadData
End If

End Select
Loop
fin.Close
fout.Close
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187442

http://www.thecodecage.com/forumz/chat.php