Trim ref text posted in Excel VBA editor

Question

enter code hereI have my following code a bit short in the process of working on it, so I am trying to get text from a .txt file to be displayed in a cell on Excel, the code will be

Sub citi()
Dim c As Range
Open "C:\Users\alvaradod\Desktop\citi macro\Import File.txt" For Input As #1
R = 0
Dim i As Range
Dim num As Integer
Dim arrData() As String
the_value = Sheets("Prog").Range("A1")
Do Until EOF(1)
Line Input #1, Data
If Not Left(Data, 1) = "" Then
'import this row
R = R + 1
Cells(R, 1).Value = Data
'Mid(the_value, 3, 5)
'Left(Data, Len(Data) - 3)).Value
End If
Loop
For Each i In Range("A1")
i.Select
ActiveCell.Rows("1:1").Mid(Data(i), 49, 5).Select
'ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("Import").Range("A1").End(xlUp).Offset(num, 0).PasteSpecial
ActiveCell.Rows.Delete
 num = num + 1
Next i

End Sub

" LINE 11 WILL PAST THE TEXT FROM LINE ONE ON .TXT FILE TO EXCEL, AFTER THIS FUNCTION I NEED TO TRIM THIS SAME TEXT IN THE EXCEL SHEET TO SHOW THE FIRST 5 CHARACTERS"

Answer 1

Your question isn't very clear, but perhaps something like this?

Sub citi()

    Dim oFSO As Object
    Dim arrData() As String
    Dim arrImport1(1 To 65000) As String
    Dim arrImport2(1 To 65000) As String
    Dim i As Long, j As Long

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    arrData = Split(oFSO.OpenTextFile("C:\Test\test.txt").ReadAll, vbCrLf)

    For i = LBound(arrData) To UBound(arrData)
        If Len(arrData(i)) > 0 Then
            j = j + 1
            arrImport1(j) = Mid(arrData(i), 3, 5)
            arrImport2(j) = Mid(arrData(i), 49, 5)
        End If
    Next i

    If j > 0 Then
        Sheets("Sheet1").Range("A1").Resize(j).Value = Application.Transpose(arrImport1)
        Sheets("Sheet2").Range("A1").Resize(j).Value = Application.Transpose(arrImport2)
    End If

    Set oFSO = Nothing
    Erase arrData
    Erase arrImport

End Sub