I am trying to extract the first set of digits only with regex function from col A in Vba.
我正在尝试从Vba中的col A中提取第一组带regex函数的数字。
PRECEDEX 200 mcg 2 mL FTV should print only 200. Currently my code prints all the numbers.
predex 200mcg2ml FTV只能打印200个。目前我的代码打印所有的数字。
Private Sub splitUpRegexPattern()
Dim Regex As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim Myrange As Range
Set Myrange = ActiveSheet.Range("E3:E1500")
For Each C In Myrange
strPattern = "\D+"
If strPattern <> "" Then
strInput = C.Value
strReplace = "$1"
With Regex
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If Regex.test(strInput) Then
C.Offset(0, 1) = Regex.Replace(strInput, " ")
Else
C.Offset(0, 1) = "(Not matched)"
End If
End If
Next
End Sub
1 个解决方案
#1
2
You should just use \d+
pattern, and use .Execute
rather than .Replace
method to actually extract the digits (you also need to use RegExp.Global=False
to find only the first match).
您应该使用\d+模式,并使用. execute而不是. replace方法来实际提取数字(还需要使用RegExp)。全局=错误,只找到第一个匹配)。
Use
使用
Sub splitUpRegexPattern()
Dim Regex As New regexp
Dim strPattern As String
Dim strInput As String
Dim Myrange As Range
Dim mtch As Object
Set Myrange = ActiveSheet.Range("E3:E1500")
For Each c In Myrange
strPattern = "\d+"
If strPattern <> "" Then
strInput = c.Value
With Regex
.Global = False
.MultiLine = True
.IgnoreCase = False
.pattern = strPattern
End With
If Regex.test(strInput) Then
Set mtch = Regex.Execute(strInput)
If mtch.Count > 0 Then
c.Offset(0, 1) = mtch.Item(0).Value
End If
Else
c.Offset(0, 1) = "(Not matched)"
End If
End If
Next
End Sub
Here, Set mtch = Regex.Execute(strInput)
tries to find the match and if a match is found (If mtch.Count > 0
), the value (mtch.Item(0).Value
) is added to the next column on the right.
在这里,设置mtch = Regex.Execute(strInput)尝试查找匹配项,如果找到了匹配项(如果找到了mtch)。计数>)),值(mtch.Item(0). value)被添加到右边的下一列。
#1
2
You should just use \d+
pattern, and use .Execute
rather than .Replace
method to actually extract the digits (you also need to use RegExp.Global=False
to find only the first match).
您应该使用\d+模式,并使用. execute而不是. replace方法来实际提取数字(还需要使用RegExp)。全局=错误,只找到第一个匹配)。
Use
使用
Sub splitUpRegexPattern()
Dim Regex As New regexp
Dim strPattern As String
Dim strInput As String
Dim Myrange As Range
Dim mtch As Object
Set Myrange = ActiveSheet.Range("E3:E1500")
For Each c In Myrange
strPattern = "\d+"
If strPattern <> "" Then
strInput = c.Value
With Regex
.Global = False
.MultiLine = True
.IgnoreCase = False
.pattern = strPattern
End With
If Regex.test(strInput) Then
Set mtch = Regex.Execute(strInput)
If mtch.Count > 0 Then
c.Offset(0, 1) = mtch.Item(0).Value
End If
Else
c.Offset(0, 1) = "(Not matched)"
End If
End If
Next
End Sub
Here, Set mtch = Regex.Execute(strInput)
tries to find the match and if a match is found (If mtch.Count > 0
), the value (mtch.Item(0).Value
) is added to the next column on the right.
在这里,设置mtch = Regex.Execute(strInput)尝试查找匹配项,如果找到了匹配项(如果找到了mtch)。计数>)),值(mtch.Item(0). value)被添加到右边的下一列。