Sorry, I misunderstood the question. Your code reads a file into an Excel worksheet and parses each line into a row of cells, where cells are delimited by either a comma or a tab. Since you're splitting each line at commas/tabs, I had assumed your input files were already essentially .csv files with a .txt extension. But your code allows for lines with a variable number of fields, so reading them as csv wouldn't work anyway, even if you wanted to go that route.
I don't know a built-in way to make the text-to-number conversion, but I'm able to convert numbers using a regular expression (regex). To use a regex, you'll need to add a reference (it's version 5.5 in my Office 2019):
Tools/References: Microsoft VBScript Regular Expressions 5.5
If you're not comfortable with regex, see How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops for an excellent reference. The pattern defined here
.Pattern = "^-?[0-9]*\.?[0-9]*$"
accepts a leading minus sign (not required), any number of digits, a decimal point (also not required), and any number of digits after the decimal (if there is a decimal).
The code tests every element of the input variant array against the regex, and converts numbers to their values. I find I have to copy the elements into a new variant for output. Excel won't allow converting the number from string to value in the original variant.
Sub ImportText3()
Dim ThisLine As Variant
Dim fileFilterPattern As String
Dim n As Long
Dim rngOutput As Range, nCells As Integer, nIndex As Integer
Dim regexNumber As RegExp, vntRc As Variant, vntOutput As Variant
fileFilterPattern = "Text Files *.txt,*.txt"
ThisLine = Application.GetOpenFilename(fileFilterPattern)
If ThisLine = False Then
MsgBox "No file selected."
Exit Sub
End If
' Regular expression to look for values
' Accept any number of digits, with possible decimal and/or minus sign, nothing else
Set regexNumber = New RegExp
With regexNumber
.Pattern = "^-?[0-9]*\.?[0-9]*$" ' Define allowed characters to determine a number
End With
Application.ScreenUpdating = False
Open ThisLine For Input As #1
Do Until EOF(1)
Line Input #1, ThisLine
If Len(ThisLine) > 0 Then
ThisLine = Replace(ThisLine, ",", Chr(9))
ThisLine = Split(ThisLine, Chr(9))
nCells = UBound(ThisLine)
' Use an output variant to avoid forcing a value into a string
ReDim vntOutput(0 To nCells)
Set rngOutput = ActiveCell.Offset(n, 0).Resize(1, nCells + 1)
' Test each element for numeric values
For nIndex = 0 To nCells
vntRc = regexNumber.Test(ThisLine(nIndex))
If vntRc = True Then
vntOutput(nIndex) = Val(ThisLine(nIndex)) ' If numeric, convert it to value
Else
vntOutput(nIndex) = ThisLine(nIndex) ' If not, copy it as is
End If
Next nIndex
rngOutput = vntOutput
End If
n = n + 1
Loop
Close #1
Application.ScreenUpdating = True
End Sub
