How do I clean this parse'd txt file up by 1: removing the “quotation marks” and 2: separating the data into two columns?

[buck johnson]

Here is my code that parse's a txt file onto a worksheet. If the txt file has more than 508 rows, it puts it onto a new worksheet.
How do I remove the quotation marks from the text, and split the data into two adjacent columns?
I've attached an example of the "txt file" below the code.
Thanks, folks
_____________
>>> use code - tags <<<

Option Explicit
Sub ParseSingleSpecimen()

Dim Result As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
Dim worksheetName As String
Dim w As Integer

FileName = "C:\Users\enxle\Desktop\SingleSpecimen.txt"

    If FileName = "" Then End
        FileNum = FreeFile()
        Open FileName For Input As #FileNum
        Application.ScreenUpdating = False
        Workbooks.Add template:=xlWorksheet
        Counter = 1

            Do While Seek(FileNum) <= LOF(FileNum)
            Application.StatusBar = "Importing Row " & _
            Counter & " of text file " & FileName
            Line Input #FileNum, Result
               
                If Left(Result, 1) = "=" Then
                ActiveCell.Value = "'" & Result
        Else
        ActiveCell.Value = Result
    End If


If ActiveCell.Row = 508 Then
    w = w + 1
    ActiveWorkbook.Sheets.Add
    worksheetName = "Specimen#" & w
    ActiveSheet.Name = worksheetName
    Else
    ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1

Loop

Close

Application.StatusBar = False

End Sub

_____________________
Example of "txt sample"
""
"Test Method" "TENSILE MASTER"
"Sample I. D." "OSB"
"Specimen Number" "1"
"EXTENSION (In)" "LOAD (Lb)"
-0.027375 3.497136
-0.027296 3.100909
-0.027158 3.479909
-0.027040 3.273182
-0.026902 3.531591
-0.026784 3.721091
-0.026646 4.134545
-0.026528 4.048409
-0.026390 4.306818
-0.026272 4.496318

 

[vletm]

buck johnson
As a new member, please reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

especially part How to get the Best Results at Chandoo.org
Hints:
... without Your sample Excel-file, there are some challenges
... without real 'txt sample'-file, there are some challenges
... without Your expected results (even done manually), there are some challenges

 

[Marc L]

How do I remove the quotation marks from the text, and split the data into two adjacent columns?

As Excel has obviously all the necessary to parse a text file into worksheet columns whatever with text doubles quotes or not​

so just activate the Macro Recorder and operate manually in order to get your own code base​

then you just will have to add the part for breaking every 508th row …​

​