vba - 替换一行VBA中的多个字符

Question

我在 VBA 中工作，有一个包含所有字母表字母的单元格，我想替换所有字母(没有任何字母，“”，或数字，“0”)，以及可能的标点符号。我目前将其运行为:

val = Replace(val, "(", "")
val = Replace(val, "]", "")
val = Replace(val, "A", "")
val = Replace(val, "B", "")
val = Replace(val, "C", "")
val = Replace(val, "D", "")
val = Replace(val, "E", "")
val = Replace(val, "F", "")
val = Replace(val, "G", "")
val = Replace(val, "H", "")
val = Replace(val, "I", "")
val = Replace(val, "J", "")
val = Replace(val, "K", "")
val = Replace(val, "L", "")
val = Replace(val, "M", "")
val = Replace(val, "N", "")
val = Replace(val, "O", "")
val = Replace(val, "P", "")
val = Replace(val, "Q", "")
val = Replace(val, "R", "")
val = Replace(val, "S", "")
val = Replace(val, "T", "")
val = Replace(val, "V", "")
val = Replace(val, "W", "")
val = Replace(val, "X", "")
val = Replace(val, "Y", "")
val = Replace(val, "Z", "")

我试过:

val = Replace(val, "(", "")
val = Replace(val, "]", "")
val = Replace(val, "[A-TV-Z]", "")

我可以添加一个循环，但计算时间不会改变。

但它不起作用。有没有办法像我尝试的那样用一行代码替换 25 行代码，因为我必须读取 100 万个单元格，而且目前运行时间很长。可悲的是，此替换始终在不同的场合运行。

Answer 1

您可以尝试使用正则表达式。我不能在单行上得到它，但它应该比你当前的版本更快并且行数更少。请注意，您需要引用 Microsoft VBScript 正则表达式库。

Public Function Replacement(sInput) As String

Dim regex As New RegExp

With regex
   .Global = True
   .IgnoreCase = False
End With

regex.pattern = "[A-Z, a-z]"
Replacement = regex.Replace(sInput, "")


End Function