If yoou have other backshlash character in your data make this change

from
Case Else
fout.write ReadData

to
Case Else
FoundBackSlash = False
fout.write ReadData

"Joel" wrote:

the code below reads your file and writes the data to a new file with the new
line added. I could put the data into a worksheet if you like.

Sub AddNewLine()

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


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)

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

Case "]":
If FoundBackSlash = True Then
FoundBackSlash = False
fout.write "]"
Else
fout.writeline "]"
End If
Case "\":
If FoundBackSlash = True Then
'incase there are 2 backslashees in a row
FoundBackSlash = False
Else
FoundBackSlash = True
End If
fout.write "\"
Case Else
fout.write ReadData
End Select
Loop
fin.Close
fout.Close
End Sub




"JohnG1965" wrote:

I have a text document that I'm trying to import into Excel and have it
formatted where the text stops at the "]" symbol and creates a new row for
the next segment.

Example: text doc starts like this:
ISA*00**00**09*00507000*000043213*0*P*\]GS*AG*47*F5332B0*090220*0506*655*X*003010]ST*824*000001110]BGN*00*1*090220]N1*ST**92*47]N1*SF**92*F5332B0]OTI*IE*SI*P045219*******856]REF*PK*PS-42637]REF*PO*HES
C93364]

I want it to transform in to this:
ISA*00**00**09*00507000*000043213*0*P*\]GS*AG*47*F5332B0*090220*0506*655*X*003010]
ST*824*000001110]
BGN*00*1*090220]
N1*ST**92*47]
N1*SF**92*F5332B0]
OTI*IE*SI*P045219*******856]
REF*PK*PS-42637]
REF*PO*HESC93364]

Is this possible?