删除特殊字符 VBA Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24356993/
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
Removing special characters VBA Excel
提问by pixie
I'm using VBA to read some TITLES and then copy that information to a powerpoint presentation.
我正在使用 VBA 阅读一些标题，然后将该信息复制到幻灯片演示文稿中。
My Problem is, that the TITLES have special characters, but Image files that I am also coping over do not.
我的问题是，标题有特殊字符，但我也在处理的图像文件没有。
The TITLE forms part of a path to load a JPEG into a picture container. E.g. "P k.jpg", but the title is called "p.k".
TITLE 构成了将 JPEG 加载到图片容器的路径的一部分。例如“P k.jpg”，但标题被称为“pk”。
I want to be able to ignore the special characters in the TITLE and just get it to see a space instead so it picks up the right JPG file.
我希望能够忽略 TITLE 中的特殊字符，而只是让它看到一个空格，以便它选择正确的 JPG 文件。
Is that possible?
那可能吗？
Thank you!
谢谢！
回答by David Zemens
What do you consider "special" characters, just simple punctuation? You should be able to use the Replacefunction: Replace("p.k","."," ").
你认为什么是“特殊”字符，只是简单的标点符号？您应该能够使用以下Replace功能：Replace("p.k","."," ")。
Sub Test()
Dim myString as String
Dim newString as String
myString = "p.k"
newString = replace(myString, ".", " ")
MsgBox newString
End Sub
If you have several characters, you can do this in a custom function or a simple chained series of Replacefunctions, etc.
如果您有多个字符，则可以在自定义函数或简单的链式Replace函数系列等中执行此操作。
Sub Test()
Dim myString as String
Dim newString as String
myString = "!p.k"
newString = Replace(Replace(myString, ".", " "), "!", " ")
'## OR, if it is easier for you to interpret, you can do two sequential statements:
'newString = replace(myString, ".", " ")
'newString = replace(newString, "!", " ")
MsgBox newString
End Sub
If you have a lot of potential special characters (non-English accented ascii for example?) you can do a custom function or iteration over an array.
如果您有很多潜在的特殊字符（例如非英文重音 ascii？），您可以执行自定义函数或对数组进行迭代。
Const SpecialCharacters As String = "!,@,#,$,%,^,&,*,(,),{,[,],},?" 'modify as needed
Sub test()
Dim myString as String
Dim newString as String
Dim char as Variant
myString = "!p#*@)k{kdfhouef3829J"
newString = myString
For each char in Split(SpecialCharacters, ",")
newString = Replace(newString, char, " ")
Next
End Sub
回答by V. Brunelle
In the case that you not only want to exclude a list of special characters, but to exclude allcharacters that are not letters or numbers, I would suggest that you use a char type comparison approach.
如果您不仅要排除特殊字符列表，还要排除所有不是字母或数字的字符，我建议您使用 char 类型比较方法。
For each character in the String, I would check if the unicode character is between "A" and "Z", between "a" and "z" or between "0" and "9". This is the vba code:
对于字符串中的每个字符，我会检查 unicode 字符是否在“A”和“Z”之间、“a”和“z”之间或“0”和“9”之间。这是vba代码：
Function cleanString(text As String) As String
Dim output As String
Dim c 'since char type does not exist in vba, we have to use variant type.
For i = 1 To Len(text)
c = Mid(text, i, 1) 'Select the character at the i position
If (c >= "a" And c <= "z") Or (c >= "0" And c <= "9") Or (c >= "A" And c <= "Z") Then
output = output & c 'add the character to your output.
Else
output = output & " " 'add the replacement character (space) to your output
End If
Next
cleanString = output
End Function
The Wikipedia list of Unicode characersis a good quick-start if you want to customize this function a little more.
如果您想更多地自定义此功能，则 Unicode 字符的Wikipedia 列表是一个很好的快速入门。
This solution has the advantage to be functionnal even if the user finds a way to introduce new special characters. It also faster than comparing two lists together.
即使用户找到了引入新的特殊字符的方法，该解决方案也具有功能性的优势。它也比比较两个列表更快。
回答by GuruKay
Here is how removed special characters.
这是删除特殊字符的方法。
I simply applied regex
我只是应用了正则表达式
Dim strPattern As String: strPattern = "[^a-zA-Z0-9]" 'The regex pattern to find special characters
Dim strReplace As String: strReplace = "" 'The replacement for the special characters
Set regEx = CreateObject("vbscript.regexp") 'Initialize the regex object
Dim GCID As String: GCID = "Text #N/A" 'The text to be stripped of special characters
' Configure the regex object
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
' Perform the regex replacement
GCID = regEx.Replace(GCID, strReplace)
回答by Ferroao
This is what I use, based on this link
这是我使用的，基于此链接
Function StripAccentb(RA As Range)
Dim A As String * 1
Dim B As String * 1
Dim i As Integer
Dim S As String
'Const AccChars = "?????àá?????èéê?ìí??D?òó???ùú?üYàáa????èéê?ìí??e?òó???ùú?üy?"
'Const RegChars = "SZszYAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyy"
Const AccChars = "?éú?í?óê??á" ' using less characters is faster
Const RegChars = "neuaicoeooa"
S = RA.Cells.Text
For i = 1 To Len(AccChars)
A = Mid(AccChars, i, 1)
B = Mid(RegChars, i, 1)
S = Replace(S, A, B)
'Debug.Print (S)
Next
StripAccentb = S
Exit Function
End Function
Usage:
用法：
=StripAccentb(B2) ' cell address
Sub version for all cells in a sheet:
工作表中所有单元格的子版本：
Sub replacesub()
Dim A As String * 1
Dim B As String * 1
Dim i As Integer
Dim S As String
Const AccChars = "?éú?í?óê??á" ' using less characters is faster
Const RegChars = "neuaicoeooa"
Range("A1").Resize(Cells.Find(what:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
Cells.Find(what:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select '
For Each cell In Selection
If cell <> "" Then
S = cell.Text
For i = 1 To Len(AccChars)
A = Mid(AccChars, i, 1)
B = Mid(RegChars, i, 1)
S = replace(S, A, B)
Next
cell.Value = S
Debug.Print "celltext "; (cell.Text)
End If
Next cell
End Sub
