vba UDF 删除单元格内的特殊字符、标点符号和空格，为 Vlookups 创建唯一键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22235830/
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
UDF to remove special characters, punctuation & spaces within a cell to create unique key for Vlookups
提问by
I hacked together the following User Defined Function in VBA that allows me to remove certain non-text characters from any given Cell.
我在 VBA 中编写了以下用户定义的函数，它允许我从任何给定的单元格中删除某些非文本字符。
The code is as follows:
代码如下：
Function removeSpecial(sInput As String) As String
Dim sSpecialChars As String
Dim i As Long
sSpecialChars = "\/:*??""?<>|.&@#(_+`?~);-+=^$!,'" 'This is your list of characters to be removed
For i = 1 To Len(sSpecialChars)
sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
Next
removeSpecial = sInput
End Function
This portion of the code obviously defines what characters are to be removed:
这部分代码显然定义了要删除的字符：
sSpecialChars = "\/:*??""?<>|.&@#(_+`?~);-+=^$!,'"
I also want to include a normal space character, " ", within this criteria. I was wondering if there is some sort of escape character that I can use to do this?
我还想在此条件中包含一个普通的空格字符“”。我想知道是否有某种转义字符可以用来执行此操作？
So, my goal is to be able to run this function, and have it remove all specified characters from a given Excel Cell, while also removing all spaces.
因此，我的目标是能够运行此函数，并让它从给定的 Excel 单元格中删除所有指定的字符，同时删除所有空格。
Also, I realize I could do this with a =SUBSTITUTE function within Excel itself, but I would like to know if it is possible in VBA.
另外，我意识到我可以在 Excel 本身中使用 =SUBSTITUTE 函数来做到这一点，但我想知道在 VBA 中是否可行。
Edit: It's fixed! Thank you simoco!
编辑：它是固定的！谢谢西莫科！
Function removeSpecial(sInput As String) As String
Dim sSpecialChars As String
Dim i As Long
sSpecialChars = "\/:*??""?<>|.&@# (_+`?~);-+=^$!,'" 'This is your list of characters to be removed
For i = 1 To Len(sSpecialChars)
sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "") 'this will remove spaces
Next
removeSpecial = sInput
End Function
采纳答案by
So after the advice from simoco I was able to modify my for loop:
因此，在 simoco 的建议之后，我能够修改我的for loop：
For i = 1 To Len(sSpecialChars)
sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "") 'this will remove spaces
Next
Now for every character in a given cell in my spreadsheet, the special characters are removed and replaced with nothing. This is essentially done by the Replace$ and Mid$ functions used together as shown:
现在，对于电子表格中给定单元格中的每个字符，特殊字符都被删除并替换为空。这基本上是由 Replace$ 和 Mid$ 函数一起使用完成的，如下所示：
sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "") 'this will remove spaces
This code is executed for every single character in the cell starting with the character at position 1, via my for loop.
此代码通过 my 对单元格中从位置 1 处的字符开始的每个字符执行for loop。
Hopefully this answer benefits someone in the future if the stumble upon my original question.
如果偶然发现我原来的问题，希望这个答案在未来对某人有益。
