替换 VBA 中的变量字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5883725/
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
Replace variable string in VBA
提问by F.P
I need to replace somethin in a string, but what to replace may vary. It can be
我需要替换字符串中的某些内容，但替换的内容可能会有所不同。有可能
XY - test
XXxY-test
XXyyXx-TEST
yXyy -Test
and virtually any other combination of whitespaces and cases of the above.
以及几乎任何其他空格和上述情况的组合。
I need to replace the "-test" part and leave the "XXX" alone. So, when using a simple replace
我需要替换“-test”部分并留下“XXX”。所以，当使用简单的替换
Replace("XXX -test", "- test", "")
It won't work, obviously. So I need a more sophisticated version of Replace that could handle this task. Is there something I could use or do I have to write it on my own?
这显然行不通。所以我需要一个更复杂的 Replace 版本来处理这个任务。有什么我可以使用的东西还是我必须自己写？
回答by eggyal
If you need more flexibility than that provided by mj82's method (for example, you may not know the length of the initial expression), you can use a regular expression. For example:
如果您需要比 mj82 的方法提供的更多灵活性（例如，您可能不知道初始表达式的长度），则可以使用正则表达式。例如：
Regex.Replace("XXX -test", "\s*-\s*test", "", RegexOptions.IgnoreCase)
回答by aevanko
This is to compliment eggyal's answer. This is a VBA regexreplace function so that you can use his answer. You'll notice I added ignore_case as an option for flexibility, so your call would be:
这是为了赞美eggyal的回答。这是一个 VBA regexreplace 函数，因此您可以使用他的答案。您会注意到我添加了 ignore_case 作为灵活性的选项，因此您的调用将是：
=RegexReplace(cell, "\s*-\s*test", "", TRUE)
Here is the function, I hope you find it useful:
这是功能，我希望你觉得它有用：
' -------------------------------------------------------------------
' Search and Replace using a regular expression
' -------------------------------------------------------------------
Function RegexReplace(ByVal text As String, _
ByVal replace_what As String, _
ByVal replace_with As String, _
Optional ByVal ignore_case As Boolean = False) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.ignorecase = ignore_case
RE.pattern = replace_what
RE.Global = True
RegexReplace = RE.Replace(text, replace_with)
End Function
回答by mj82
If XXX is first, you can use Left(string; 3) function tu cut 3 (or any length you need) letters from left side, then you don't care what's after it.
如果 XXX 是第一个，您可以使用 Left(string; 3) 函数从左侧剪切 3 个（或您需要的任何长度）字母，然后您就不必关心它后面是什么。
