如何从字符串中删除除句点和空格以外的所有非字母数字字符? [英] How to remove all non alphanumeric characters from a string except period and space in excel?

问题描述

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

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.

推荐答案

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

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

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

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

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).

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

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:

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

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

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

这篇关于如何从字符串中删除除句点和空格以外的所有非字母数字字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！