去除VBA Excel中的特殊字符

Question

我正在使用VBA读取一些标题信息，然后将这些信息复制到PowerPoint演示文稿中。

问题在于标题里有特殊字符，但是我也要复制图片文件，而图片文件没有特殊字符。

标题是打开JPEG图像的路径的一部分，例如P k.jpg，但是实际标题是p.k。

我想忽略标题中的特殊字符，只让它识别为空格，以便它能够选择正确的JPG文件。

我该怎么做？

- pixie

Answer 1

你认为哪些是“特殊”的字符，仅仅是简单的标点符号吗？你可以使用Replace函数：Replace("p.k","."," ")。

Sub Test()
Dim myString as String
Dim newString as String

myString = "p.k"

newString = replace(myString, ".", " ")

MsgBox newString

End Sub

如果您有多个字符，可以在自定义函数或简单的链式 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

如果你有大量潜在的特殊字符（例如非英语重音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

- David Zemens

---

很棒的文章，但它对于字符“?”无效。 - WJA

@JohnAndrews 如果你修改 SpecialCharacters 字符串，使问号成为其中的一部分，那么它就会生效：Const SpecialCharacters As String = "!,@,#,$,%,^,&,*,(,),{,[,],},?"。 - David Zemens

我认为列表中唯一缺失的字符是逗号本身！但我知道这需要一些额外的调整来适应该字符。 - Marcucciboy2

为了同时删除逗号，将第一行替换为 Const SpecialCharacters As String = "! @ # $ % ^ & * ( ) { [ ] } ? ,"，并在 for 循环语句中使用 For each char in Split(SpecialCharacters, " ")。你可以在这两个语句中将空格替换为任何其他字符，例如 _。 - Friedrich

有什么提示可以用"_"替换空格吗？我已经在字符列表中添加了一个空格，但出于某种原因它不起作用。 - Alexis.Rolland

该函数将分隔列表中的每个项替换为一个空格（“ ”）。请查看字符串“Replace”方法的文档，并根据需要进行更新；但是，您需要一些if / then或其他条件块来处理空格替换，因为按照原始设计，该函数将用空格替换任何指定的字符，如果当前实现，则最终会用空格替换空格 :) - David Zemens

Answer 2

如果您不仅想排除一组特殊字符，而是要排除所有不是字母或数字的字符，我建议您使用char类型比较方法。

对于字符串中的每个字符，我会检查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

维基百科的Unicode字符列表是一个很好的起点，如果你想对此函数进行更多的自定义。

这个解决方案的优点在于，即使用户找到一种引入新特殊字符的方法，它也可以正常运行。它比将两个列表进行比较更快。

- V. Brunelle

---

如果单元格中的字符数较少，则此方法比正则表达式快得多。对于包含20个字符的单元格，速度大约快15倍。当字符数约为2000时，它们的速度大致相同，但对于更高的字符数，正则表达式更快。 - Tam Le

Answer 3

这是如何删除特殊字符的方法。

我只是应用了正则表达式。

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)

- GuruKay

---

不在OSX上工作。 请参见：https://dev59.com/l6Hia4cB1Zd3GeqPSmPn - mbunch

Answer 4

这是我使用的内容，基于此链接

。

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 = "ŠŽšžŸÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ"
'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

使用方法：

=StripAccentb(B2) ' cell address

为工作表中的所有单元格设置子版本：

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

- Ferroao

Answer 5

根据Ferroao的长字符列表，我正在使用这个函数：

Function replaceSpecialCharacters(str)
Dim badCharacters, goodCharacters As Variant
Dim i As Integer
badCharacters = Array("Š", "Ž", "š", "ž", "Ÿ", "À", "Á", "Â", "Ã", "Ä", "Å", "Ç", "È", "É", "Ê", "Ë", "Ì", "Í", "Î", "Ï", "Ð", "Ñ", "Ò", "Ó", "Ô", "Õ", "Ö", "Ù", "Ú", "Û", "Ü", "Ý", "à", "á", "â", "ã", "ä", "å", "ç", "è", "é", "ê", "ë", "ì", "í", "î", "ï", "ð", "ñ", "ò", "ó", "ô", "õ", "ö", "ù", "ú", "û", "ü", "ý", "ÿ")
goodCharacters = Array("S", "Z", "s", "z", "Y", "A", "A", "A", "A", "A", "A", "C", "E", "E", "E", "E", "I", "I", "I", "I", "D", "N", "O", "O", "O", "O", "O", "U", "U", "U", "U", "Y", "a", "a", "a", "a", "a", "a", "c", "e", "e", "e", "e", "I", "I", "I", "I", "d", "n", "o", "o", "o", "o", "o", "u", "u", "u", "u", "y", "y")

For i = 0 To UBound(badCharacters)
  str = Replace(str, badCharacters(i), goodCharacters(i))
Next i

replaceSpecialCharacters = str

End Function

从任何其他地方调用该函数

Debug.Print replaceSpecialCharacters("žÃÌý")

或者

BadString = "žÃÌý"
GoodString =  replaceSpecialCharacters(BadString)

附注：
这种方法是用最接近的匹配字符替换特殊字符，比如将ž替换为z。

附言：
楼主提到了一个特殊字符“.”，它也可以包含在两个数组中，这样“.”就可以被替换为“ ”。

- Peter

---

提示：不必列出所有的“特殊字符”，你可以通过在字母数组Application.Match(curChar, abc)中匹配任何给定/当前字符来获益；省略可选的第三个参数可以返回有序字母列表中最接近的字母值位置，即无重音的基本字母~~>请参阅我的帖子用非重音对应字符替换带有插入符号的不同类型字符。 - undefined