替换VBA中的多个字符实例

  • Z时代
  • 2024-01-10
  • 分类：问答

我有下面的公式，我想将其转换为VBA中的函数。替换VBA中的多个字符实例

=IFERROR(LEFT(B1,(FIND(",",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"(",","),"-",","),"/",","))-1)),SUBSTITUTE(B1,".", ""))

我已经尝试了一些东西，但只是不能让它正常工作。 Test

Function CharacterSwap(text) 
Dim Old As Variant 
Dim Comma As Variant 
Old = Array(")", "(", "-", "/") 
Comma = Array(",", ",", ",", ",") 
For i = LBound(Old) To UBound(Comma) 
    If InStr(1, text, Old(i), vbBinaryCompare) > 0 Then 
     CharacterSwap = Replace(text, Old(i), Comma(i), 1, 1, vbBinaryCompare) 
    End If 
Next i 
End Function 
Function SCRUB(text) 
If InStr(1, CharacterSwap(text), ",", vbBinaryCompare) > 0 Then 
SCRUB = Left((CharacterSwap(text)), InStr(1, (CharacterSwap(text)), ",") - 1) 
Else 
SCRUB = text 
End If 
End Function

回答：

Function CharacterSwap(text) 
Dim Old As Variant 
Dim Comma As Variant 
Old = Array(")", "(", "-", "/") 
Comma = Array(",", ",", ",", ",") 
For i = LBound(Old) To UBound(Old) 
    If InStr(1, text, Old(i), vbBinaryCompare) > 0 Then 
     'adjust the text in the text variable to maintain the changes 
     text = Replace(text, Old(i), Comma(i), 1, 1, vbBinaryCompare) 
    End If 
Next i 
CharacterSwap = text 
End Function 
Function SCRUB(text) 
Dim newtext As String 
'put output into a variable to avoid the multiple calls 
newtext = CharacterSwap(text) 
If InStr(1, newtext, ",", vbBinaryCompare) > 0 Then 
    SCRUB = Left(newtext, InStr(1, newtext, ",") - 1) 
Else 
SCRUB = text 
End If 
End Function

但是这个简单的功能将尽一切较少的线路：

Function Scrub(text) 
With Application 
    Scrub = Left(text, .Min(.Find(Array("(", ")", "-", "/", ","), text & "()-/,")) - 1) 
End With 
End Function

回答：

您也可以在VBA使用正则表达式做到这一点。 如果我理解正确，您希望用逗号替换字符串中的()/-集合中的任何/所有字符。

Option Explicit 
Function CharacterSwap(S As String) As String 
    Dim RE As Object 
Set RE = CreateObject("vbscript.regexp") 
With RE 
    .Pattern = "[()/-]" 
    .Global = True 
    CharacterSwap = .Replace(S, ",") 
End With 
End Function

如果你碰巧字符.Pattern添加到字符类以上，你应该知道的是破折号-必须是在课堂上列出的第一个或最后一个字符。

如果它在其他地方，它将被解释为表示由前后字符界定的一系列字符。

换句话说

  • [0-9]将包括所有的数字。

  • [-09]将只包括dash，0和9

以上是 替换VBA中的多个字符实例 的全部内容， 来源链接： utcz.com/qa/267160.html

回到顶部