VBA 排除特殊字符和数字但保留字符串中的空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20736071/
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
VBA Exclude special characters and numbers but keep spaces from string
提问by CloseISQ
I have the text "abcd 24 ef-gh" in an excel cell.
我在 Excel 单元格中有文本“abcd 24 ef-gh”。
I need to get rid of "24" and "-" but keep all letters and spaces.
我需要去掉“24”和“-”，但保留所有字母和空格。
I have a function that loops through the text and identifies letters using the like operator. For some reason, it doesn't exclude the "24" nor the "-" for which I added a replace statement before the loop:
我有一个循环遍历文本并使用 like 运算符识别字母的函数。出于某种原因，它不排除“24”或“-”，我在循环之前为其添加了替换语句：
Function StripNonAlpha(TextToReplace As String) As String
Dim i As Integer
Dim a, b, c As String
a = Replace(TextToReplace, "-", " ")
For i = 1 To Len(a)
b = Mid(a, i, 1)
If b Like "[A-Z,a-z, ]" Then
c = c & b
End If
Next i
StripNonAlpha = c
End Function
I would have liked to use regex instead of this loop, but I didn't figure out the regex to use with VBA when I found a lot of examples for other languages.
我本来希望使用正则表达式而不是这个循环，但是当我发现很多其他语言的示例时，我没有弄清楚要与 VBA 一起使用的正则表达式。
Any regex suggestion is welcome, but I would also like to know why my loop is not behaving as expected.
欢迎任何正则表达式建议，但我也想知道为什么我的循环没有按预期运行。
回答by brettdj
Use a simple regexplike this to keep your desired characters (ie remove anything that is not a-z, A-Zor a space)
使用像这样的简单正则表达式来保留您想要的字符（即删除任何不是a-z，A-Z或空格的内容）
Update:edit made to replace -with " "prior to regexp stripping
更新：编辑制作，以取代-与" "之前的正则表达式剥离
Function StripNonAlpha(TextToReplace As String) As String
Dim ObjRegex As Object
Set ObjRegex = CreateObject("vbscript.regexp")
With ObjRegex
.Global = True
.Pattern = "[^a-zA-Z\s]+"
StripNonAlpha = .Replace(Replace(TextToReplace, "-", Chr(32)), vbNullString)
End With
End Function
