vba 如何从字符串中删除除句点和空格以外的所有非字母数字字符？

How to remove all non alphanumeric characters from a string except period and space in excel?

提问by xyz

I need to remove all non alphanumeric characters from a string except period and space in Excel. A solution using VBA rather than pure excel functions be just fine.

我需要从字符串中删除除 Excel 中的句点和空格之外的所有非字母数字字符。使用 VBA 而不是纯 excel 函数的解决方案就好了。

回答by Peter Albert

Insert this function into a new module in the Visual Basic Editor:

将此函数插入​​到 Visual Basic 编辑器中的新模块中：

Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String

    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function

Now you can use this as a User Define Function, i.e. if your data is in cell A1, place this formula in an empty cell =AlphaNumericOnly(A1).

现在您可以将其用作用户定义函数，即如果您的数据在单元格中A1，则将此公式放在空单元格中=AlphaNumericOnly(A1)。

If you want to convert a large range directly, i.e. replace all the non-alphanumeric characters without leaving the source, you can do this with another VBA routine:

如果要直接转换大范围，即在不离开源的情况下替换所有非字母数字字符，则可以使用另一个 VBA 例程执行此操作：

Sub CleanAll()
    Dim rng As Range

    For Each rng In Sheets("Sheet1").Range("A1:K1500").Cells 'adjust sheetname and range accordingly
        rng.Value = AlphaNumericOnly(rng.Value)
    Next
End Sub

Simply place this sub in the same module and execute it. Be aware though, that this will replace any formulas in the range.

只需将这个 sub 放在同一个模块中并执行它。但请注意，这将替换该范围内的任何公式。

回答by ashleedawg

Here' an alternate method of removing "whatever characters you want" from a string using pattern matching.

这是使用模式匹配从字符串中删除“您想要的任何字符”的另一种方法。

• The example below removes everything exceptletters, numbers, spaces and periods ([A-Z.a-z 0-9])

• For improved efficiency it also utilizes VBA's seamless conversionbetween Strings and Byte Arrays:

• 下面的示例删除除字母、数字、空格和句点 ( [A-Z.a-z 0-9]) 之外的所有内容

• 为了提高效率，它还利用了 VBA在字符串和字节数组之间的无缝转换：

cleanStringFunction:

Function cleanString(str As String) As String
    Dim ch, bytes() As Byte: bytes = str
    For Each ch In bytes
        If Chr(ch) Like "[A-Z.a-z 0-9]" Then cleanString = cleanString & Chr(ch)
    Next ch
End Function

cleanString功能：

Function cleanString(str As String) As String
    Dim ch, bytes() As Byte: bytes = str
    For Each ch In bytes
        If Chr(ch) Like "[A-Z.a-z 0-9]" Then cleanString = cleanString & Chr(ch)
    Next ch
End Function

---

---

More Information:

更多信息：

• For more about creating patterns for the Likeoperator, see:
  • VBA: Like Operatordescription
  • better info in the VB.NET: Like Operatordescription
• More about how Byte Arrays and Strings are basically interchangeable

• 有关为Like运算符创建模式的更多信息，请参阅：
  • VBA：喜欢运算符描述
  • VB.NET 中的更好信息：Like Operatordescription
• 更多关于字节数组和字符串如何基本上可以互换

回答by Nigel Foster

I was looking for a more elegant solution than the one I came up with. I was going to use ashleedawg's code above as it certainly is neater than my code. Ironically, mine ran 30% quicker. If speed is important (say you have a few million to do), try this:

我正在寻找一种比我想出的更优雅的解决方案。我打算使用上面 ashleedawg 的代码，因为它肯定比我的代码更简洁。具有讽刺意味的是，我的运行速度快了 30%。如果速度很重要（假设你有几百万要做），试试这个：

    Public Function AlphaNumeric(str As String) As String
    Dim i As Integer

    For i = 1 To Len(str)
        If InStr(1, "01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz. ", Mid(str, i, 1)) Then AlphaNumeric = AlphaNumeric & Mid(str, i, 1)
    Next
End Function

There's a surprise around every corner with VBA. I'd never imagine this would be quicker...

VBA 的每个角落都有惊喜。我从来没有想过这会更快......

回答by Andoni

I have written the following code and it works as far as I tested it, it consists of two functions. The first checks whether a string is alphanumeric and the second makes the replacement (it also removes spaces)

我编写了以下代码，就我测试过的而言，它可以工作，它由两个函数组成。第一个检查字符串是否为字母数字，第二个进行替换（它还删除空格）

Public Function Isalphanumeric(cadena As String) As Boolean

    Select Case Asc(UCase(cadena))
        Case 65 To 90 'letras
            Isalphanumeric = True
        Case 48 To 57 'numeros
            Isalphanumeric = True
        Case Else
            Isalphanumeric = False

    End Select

End Function

And here goes the remove function

这是删除功能

Function RemoveSymbols_Enhanced(InputString As String) As String

 Dim InputString As String
 Dim CharactersArray()
 Dim i, arrayindex, longitud As Integer
 Dim item As Variant


 i = 1
 arrayindex = 0
 longitud = Len(InputString)

'We create an array with non alphanumeric characters
 For i = 1 To longitud

  If Isalphanumeric(Mid(InputString, i, 1)) = False Then
    ReDim Preserve CharactersArray(arrayindex)
    CharactersArray(arrayindex) = Mid(InputString, i, 1)
    arrayindex = arrayindex + 1

  End If

  Next

 'For each non alphanumeric character we do a replace
 For Each item In CharactersArray
  item = CStr(item)
  InputString = Replace(InputString, item, "")
 Next


End Function