If your file is a Word document (you show a .doc extension of your
filename), then I'm not sure how you would do what you want. However, if
that is really a pure text document (normally having a .txt extension), then
you can use this macro to do what you want. Change the two Const statement
assignments to match your actual conditions (cell address for the start of
your list and the full path plus filename for the file you want to import).

Sub BreakTextUp()
Dim X As Long
Dim FileNum As Long
Dim TotalFile As String
Dim LinesOfData() As String

Const StartCellAddress As String = "B3"
Const PathAndFileName As String = "c:\temp\text.txt"

FileNum = FreeFile
Open PathAndFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
TotalFile = Replace(TotalFile, "]", "]" & vbLf)
LinesOfData = Split(TotalFile, vbLf)
For X = 0 To UBound(LinesOfData)
ActiveSheet.Range(StartCellAddress).Offset(X).Valu e = LinesOfData(X)
Next
End Sub

--
Rick (MVP - Excel)


"JohnG1965" wrote in message
...
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?