How to read numbers from a text file which has both text and numbers from VBA?

Question

3.00/5 (1 vote)

See more:

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

VB

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.

Posted 24-May-16 7:51am

Member 12508867

Updated 30-May-16 16:33pm

Patrice T

v7

Add a Solution

Comments

Patrice T 24-May-16 14:14pm

Show that you can do something !
At least show some code that can read the data file.

Maciej Los 24-May-16 14:34pm

Record macro!

3 solutions

Solution 3

In link Excel Regex Tutorial (Regular Expressions) - The Analyst Cave | Excel, VBA, programming and more[^], look at Regex: Match pattern in a string with pattern "\d\.\d{2}". Must be an interesting approach too.

This may help you to check that your RegEx do what you expect.
perlre - perldoc.perl.org[^]
Debuggex: Online visual regex tester. JavaScript, Python, and PCRE.[^]

Posted 25-May-16 11:36am

Patrice T

Solution 4

Well, its done thanks to all for helping. I have used execute property of RegExp which stores the searched pattern in array and then you can easily access any number (or whatever you are searching for) you want.

Posted 30-May-16 16:33pm

Member 12508867

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-05-24T08:03:00

Solution 1

Start here: vba - How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops - Stack Overflow[^]
And then try this as a regex:

(\s\d+(\.\d+)?)

It extracts the numbers (and just the numbers) into separate matches, so you can process them separately.

Posted 24-May-16 8:03am

OriginalGriff

Comments

Member 12508867 25-May-16 14:10pm

It helped.
After a few trails and modificatins 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.