My text file looks somewhat like
xyz sv th 1.00 1.11 1.12
abcd fd yg qw 2.00 2.11 2.12
pqr the 3.00 3.11 3.12
These texts are kind of heading and the values are for different cases for all.
Now I want to read these numbers (only numbers) from text file and write them into an excel sheet.
I know how to read a text file from VBA and write it into excel sheet, but I'm unable to think of the code which gives me only numbers. Also could there be way by which I can write all the text including space in a column and the numbers in different columns,but these spaces in between the text are creating problem.
Do help.
Thanks
What I have tried:
Sorry couldn't come up with any code to do this.
Help Needed
EDIT 1:
After a few trails I have come up with this code
Sub textfile()
Dim ptrn1 As String: ptrn1 = "^\w*\W*"
Dim ptrn2 As String: ptrn2 = "^\d\.\d{2}"
Dim reg As New RegExp
Dim strInput As String
strInput = "Xyz Abcd 12 pqrst 1.11 2.11 3.11"
If ptrn1 <> "" Then
For i = 1 To 10
With reg
.Global = True
.IgnoreCase = False
.Pattern = ptrn1
End With
If reg.Test(strInput) Then
strInput = (reg.Replace(strInput, ""))
End If
With reg
.Global = True
.IgnoreCase = False
.Pattern = ptrn2
End With
If reg.Test(strInput) Then
Exit For
End If
Next i
MsgBox (strInput)
End If
End Sub
The string here in strInput is the type of text lines I have to deal with and have to extract the last three numbers (or many more). Now this code gives me the string "1.11 2.11 3.11" as OUTPUT from which I can extract the numbers needed as they all have specific pattern from here i.e., \d\.\d{2} ,but the initial text does not have any specific pattern it could be as "Xyz Abcd 12 pqrst" or "XY Sd" or "PQR 11 12 qwe".
So, could there be any easy way to do this, I mean could I get those numbers directly without first chopping of the text line and then again processing it as me text file have a lot many numbers of such lines.