How to arrange the unformatted text in table format

[KRIXXXX]

I have to deal with unformatted text files which contains transactions. I am attaching here sample of this text file in Sheet1 which contains many data which I don’t need. I have manually arranged data of this unformatted text file data in the manner I need in Sheet2. Kindly provide the solution to arrange this data.

[kvsrinivasamurthy]

Try to paste the data from unformatted text to wordpad. From Wordpad copy and paste it worksheet and upload that file.

[KRIXXXX]

new excel file attached which contains data pasted from wordpad file

[kvsrinivasamurthy]

Code for macro

Sub RearrangeData()
Dim Lr As Long, T As Long, X As Long
Dim A, D

Lr = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
ReDim B(1 To Lr)
ReDim C(1 To Lr)

With Sheets("Sheet1")
A = .Range("A1:A" & Lr)

For T = 1 To Lr
If Left(A(T, 1), 1) <> "-" And Mid(A(T, 1), 3, 1) = "-" Then
X = X + 1
B(X) = A(T, 1)
If InStr(1, B(X), "/") = 0 And Not IsNumeric(Mid(B(X), 10, 1)) Then B(X) = Left(B(X), 8) & " " & "0" & " " & "0" & " " & Mid(B(X), 40)

B(X) = Replace(B(X), WorksheetFunction.Rept(" ", 40), " " & "0" & " " & "0" & " ")

B(X) = Replace(B(X), WorksheetFunction.Rept(" ", 12), " " & "0" & " ")

If Left(A(T + 2, 1), 1) <> "" Then C(X) = Trim(A(T + 2, 1))
End If
Next T
End With

With Sheets("Sheet3").Range("A2:A" & X + 1)
.CurrentRegion.Clear
.Value = WorksheetFunction.Transpose(B)
.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
        TrailingMinusNumbers:=True
.Offset(0, 7).Formula = "=DATE(1*(""20""&MID(A2" & ",7,2)),1*" & "MID(A2" & ",4,2),1*" & "MID(A2" & ",1,2))"
.Offset(0, 7).Value = .Offset(0, 7).Value
.Offset(0, 7).Copy Sheets("Sheet3").Range("A2")
.Offset(0, 7).Value = ""
End With

T = 0
With Sheets("Sheet3")
D = .Range("B2:B" & X + 1)

For T = 2 To UBound(D, 1)
D(T, 1) = D(T, 1) & C(T)
Next T
.Range("B2:B" & X + 1) = D
.Range("A1:F1") = Array("DATE", "PARTICULARS", "CHQ.NO.", "WITHDRAWALS", "DEPOSITS", "BALANCE")

End With

End Sub

This code is tailored with guess and approximation for given data. Pl check thoroughly with other data before using.