Counting character position after two or more spaces

you can use the following function to get the length of the columns from the header row:

Function GetF_LENs(txt As Variant, nCols As Long) As Variant
    Dim t As Variant
    Dim iFLEN As Long

    t = Split(WorksheetFunction.Trim(txt), " ")
    nCols = UBound(t) + 1 '<--| the number of columns equals the number of found values
    ReDim FLENs(1 To nCols - 1) '<--| we need the width of columns till the one before the last column
    For iFLEN = 1 To nCols - 1
        FLENs(iFLEN) = InStr(txt, t(iFLEN))
    Next
    GetF_LENs = FLENs
End Function

      

        
        
        
      

    

and you can use it in your code like this:

Sub exporttosheet()        
    Const fsoForReading = 1

    Dim fPath As String
    fPath = "C:\test.txt"

    Dim F_LENs As Variant, txt As Variant        
    Dim objFSO As Object, objTextStream As Object
    Dim rw As Long, nCols As Long

    Set objFSO = CreateObject("scripting.filesystemobject")
    Set objTextStream = objFSO.OpenTextFile(fPath, fsoForReading)

    txt = objTextStream.Readline '<--| read the first "header" line
    F_LENs = GetF_LENs(txt, nCols) '<--| get 'F_LENs' array out of "header" line: it stores the widths of all columns
    ReDim values(1 To nCols) '<--| resize the array that will hold each row values accordingly to the number of columns encountered
    rw = 1
    Do Until objTextStream.AtEndOfStream
        ReadValuesAndWriteCells txt, F_LENs, values, nCols, rw
        txt = objTextStream.Readline '<--| read the first "header" line
    Loop
    ReadValuesAndWriteCells txt, F_LENs, values, nCols, rw

    objTextStream.Close
End Sub

      

        
        
        
      

    

where i typed the current line, reading and writing the next sub

Sub ReadValuesAndWriteCells(txt As Variant, F_LENs As Variant, values As Variant, nCols As Long, rw As Long)
    Dim start As Integer
    Dim fLen As Integer

    start = 1
    For fLen = 1 To nCols - 1 '<--| loop through 'F_LENs' array, i.e.: through current line columns
        values(fLen) = Trim(Mid(txt, start, F_LENs(fLen) - start)) '<-- store current line current column value in corresponding 'Values' index
        start = F_LENs(fLen)
    Next
    values(fLen) = Trim(Mid(txt, start)) '<-- store current line last column value

    With ThisWorkbook.Sheets("data").Cells(rw, 1).Resize(1, nCols)
        .NumberFormat = "@" 'format cells as text
        .Value = values '<--| write current line array values
    End With
    rw = rw + 1
End Sub