如何从包含VBA文本和数字的文本文件中读取数字？ [英] How to read numbers from a text file which has both text and numbers from VBA?

问题描述

我的文本文件看起来有点像

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

这些文字是标题，价值适用于所有人的不同情况。

现在我想读这些数字（只有数字） ）从文本文件中将它们写入excel表。

我知道如何从VBA读取文本文件并将其写入Excel工作表，但我无法想到给我的代码只有数字。我也可以通过这种方式编写所有文本，包括列中的空格和不同列中的数字，但文本之间的这些空格会产生问题。

请求帮助。

谢谢



我的尝试：



抱歉无法提供任何代码来执行此操作。

需要帮助



编辑1：

经过几段小道后我得出了这段代码

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

strInput中的字符串是我必须处理的文本行的类型，必须提取最后三个数字（或更多）。现在这段代码给了我字符串1.11 2.11 3.11作为OUTPUT我可以从中提取所需的数字，因为它们都有来自这里的特定模式，即\\\\\\ {2}，但是初始文本确实如此没有任何特定的模式可能是Xyz Abcd 12 pqrst或XY Sd或PQR 11 12 qwe。

那么，有没有简单的方法可以做到这一点，我的意思是我可以直接获取这些数字而不先切断文本行然后再处理它，因为我的文本文件有很多这样的行。

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.

推荐答案

从这里开始： vba - 如何在Microsoft Excel中使用正则表达式（正则表达式）在单元格和循环中 - 堆栈溢出 [ ^ ]

然后尝试将其作为正则表达式：

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.

在链接 Excel正则表达式教程（正则表达式） - 分析师洞穴| Excel，VBA，编程等 [ ^ ] ，查看正则表达式：字符串中的匹配模式与模式\\\\\\ {2}。一定也是一个有趣的方法。





这可以帮助您检查您的RegEx是否符合预期。

perlre - perldoc.perl.org [ ^ ]

Debuggex：在线视觉正则表达式测试仪。 JavaScript，Python和PCRE。 [ ^ ]

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.[^]

嗯，这完全归功于所有人的帮助。我使用了RegExp的execute属性，它将搜索到的模式存储在数组中，然后你可以轻松访问你想要的任何数字（或者你正在搜索的任何数字）。

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.

这篇关于如何从包含VBA文本和数字的文本文件中读取数字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！