你知道这段代码有什么问题吗?它应该提取所有大写字母和模式"1WO"(如果可用)。 例如,在"User:399595:Account:ETH:balance"中,我应该有"UAETH",而在"User:197755:Account:1WO:balance"中,我应该有"UA1WO">
谢谢
Option Explicit
Function ExtractCap(Txt As String) As String
Application.Volatile
Dim xRegEx As Object
Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
If xRegEx.Pattern = "[^A-Z]" Then
xRegEx.Global = True
xRegEx.MultiLine = False
ExtractCap = xRegEx.Replace(Txt, "")
Set xRegEx = Nothing
Else: xRegEx.Pattern = "1WO"
ExtractCap = xRegEx.Execute(Txt)
End If
End Function
我不是"正则表达式"专家,所以你可能想尝试一个替代方案:
Function ExtractCap(Txt As String) As String
Application.Volatile
Dim i As Long
For i = 1 To Len(Txt)
Select Case Asc(Mid(Txt, i, 1))
Case 65 To 90
ExtractCap = ExtractCap & Mid(Txt, i, 1)
End Select
Next
End Function
同时,如果数据模式严格按照所示所示,您还可以考虑:
Function ExtractCap(Txt As String) As String
Application.Volatile
ExtractCap = "UA" & Split(Txt, ":")(3)
End Function
您的正则表达式的工作方式如下:
Function ExtractCap(Txt As String) As String
Application.Volatile
Dim xRegEx As Object
Set xRegEx = CreateObject("VBScript.RegExp")
With xRegEx
.Pattern = "[^A-Z]"
.Global = True
.MultiLine = False
ExtractCap = .Replace(Txt, vbNullString)
End With
If Txt = ExtractCap Then ExtractCap = "1WO"
End Function
Public Sub TestMe()
Debug.Print ExtractCap("User:399595:Account:ETH:balance")
End Sub
在您的代码中,有 2 个错误,这些错误停止了执行:
- xRegEx 设置为
Nothing
,然后要求它提供一个值; - 检查
If xRegEx.Pattern = "[^A-Z]"
实际上对VBA
意义不大。 例如,您正在设置Pattern
并从中制定条件。如果你想知道正则表达式中是否存在模式,你应该比较两个字符串 - 在模式执行之前和之后。
您的问题可以轻松解决。
首先,我假设1WO
最多可以在您的字符串中出现一次。
基于该假设,逻辑如下:
定义函数,从字符串中提取所有大写字母。
现在,在 main 函数中,您首先使用1WO
作为分量计来拆分字符串。现在,传递每个字符串(拆分后)以运行,从这些字符串中获取所有大写字母,并再次将它们连接起来,并在其位置1WO
。
Option Explicit
Public Function Extract(str As String) As String
Dim s As Variant
For Each s In Split(str, "1WO")
'append extracted caps with 1WO at the end
Extract = Extract & ExtractCaps(s) & "1WO"
Next
'delete lest 1WO from result
Extract = Left(Extract, Len(Extract) - 3)
End Function
Function ExtractCaps(str As Variant) As String
Dim i As Long, char As String
For i = 1 To Len(str)
char = Mid(str, i, 1)
If Asc(char) > 64 And Asc(char) < 91 And char = UCase(char) Then
ExtractCaps = ExtractCaps & char
End If
Next
End Function
如果将此代码放在插入的Module
中,则可以在公式中的工作表中使用它:=Extract(A1)
。