vba 如何从vba中的字符串中提取数字组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15369485/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to extract groups of numbers from a string in vba
提问by Chipsgoumerde
I have a string of the following shape:
我有以下形状的字符串：
RRP 90 AVE DE GAULLE 92800 PUTEAUX 0109781431-0149012126
The numbers might be seperated by other chars than hyphens (eg spaces). I know how to differentiate them afterwards with len().
数字可能由连字符以外的其他字符（例如空格）分隔。我知道如何用 len() 来区分它们。
I need every string of numbers to be stored seperately (in an array for example), so that I can discriminate them with len() and then use them.
我需要单独存储每个数字字符串（例如在数组中），以便我可以用 len() 区分它们，然后使用它们。
I have found how to strip the characters away from the string : How to find numbers from a string?
But it doesn't suit my problem...
但它不适合我的问题...
Could you direct me to a function or bit of code that could help me with that?
你能指导我找到一个可以帮助我的函数或代码吗？
回答by scott
This will run much faster than looping
这将比循环运行得快得多
Public Function NumericOnly(s As String) As String
Dim s2 As String
Dim replace_hyphen As String
replace_hyphen = " "
Static re As RegExp
If re Is Nothing Then Set re = New RegExp
re.IgnoreCase = True
re.Global = True
re.Pattern = "[^0-9 -]" 'includes space, if you want to exclude space "[^0-9]"
s2 = re.Replace(s, vbNullString)
re.Pattern = "[^0-9 ]"
NumericOnly = re.Replace(s2, replace_hyphen)
End Function
回答by Chipsgoumerde
Try below code :
试试下面的代码：
Function parseNum(strSearch As String) As String
' Dim strSearch As String
'strSearch = "RRP 90 AVE DE GAULLE 92800 PUTEAUX 0109781431-0149012126"
Dim i As Integer, tempVal As String
For i = 1 To Len(strSearch)
If IsNumeric(Mid(strSearch, i, 1)) Then
tempVal = tempVal + Mid(strSearch, i, 1)
End If
Next
parseNum = tempVal
End Function
回答by Nonsingular
So I realize this was a long time ago... but I was looking for a similar solution online.
所以我意识到这是很久以前的事了……但我一直在网上寻找类似的解决方案。
Some previous history on my programming skillz (sic): I started with Python and with Python I have a handy tool called List. VBA doesn't have this, so what I'm left with is something that I can input in a variable I called samplebelow, i.e. sample = [1,4,5].
关于我的编程技能的一些以前的历史（原文如此）：我从 Python 开始，使用 Python 我有一个名为List. VBA 没有这个，所以我剩下的是我可以在sample下面调用的变量中输入的东西，即sample = [1,4,5].
Back to the small code. I made it so holderwould only contain groups of numbers, as how you specified they should be grouped.
回到小代码。我让它holder只包含数字组，就像你指定它们应该分组的方式一样。
Dim count, count1 As Integer
Dim holder As String
Dim sample, smallSample As String
count = 0
count1 = 1
sample = "1ab33 efa 123 adfije-23423 123124-23423"
holder = ""
Do While count <> Len(sample)
smallSample = Left(sample, 1)
If smallSample = "0" Or smallSample = "1" Or smallSample = "2" Or smallSample = "3" Or smallSample = "4" Or smallSample = "5" Or smallSample = "6" Or smallSample = "7" Or smallSample = "8" Or smallSample = "9" Then
holder = holder & smallSample
Else
If holder <> "" Then
Cells(count1,1) = holder
count1 = count1 + 1
End If
holder = ""
End If
sample = Right(sample, Len(sample) - 1)
Loop
The output I got was
我得到的输出是
1
33
123
23423
123124
1
33
123
23423
123124
after I ran the code.
在我运行代码之后。
回答by Joel Dickey
If you are getting the Error "Issue while compiling, user defined type not defined" while using @Scotts Answer Enable the Regular Expression option as seen in Step 1 and 2 here: How to Use/Enable (RegExp object) Regular Expression using VBA (MACRO) in word(Works in Excel also)
如果您在使用 @Scotts 时遇到错误“编译时出现问题，未定义用户定义的类型”请在此处启用正则表达式选项，如步骤 1 和 2 中所示：如何使用/启用（RegExp 对象）正则表达式使用 VBA（ MACRO）在word中（也可以在Excel中使用）
P.s. Scotts Solution worked well for me.
Ps Scotts 解决方案对我来说效果很好。
