如何在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.

我需要从一个字符串中除去所有非字母数字字符，除了在Excel中的时间和空间。使用VBA而不是纯excel函数的解决方案是很好的。

2 个解决方案

#1

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中，那么将这个公式放在空单元格=字母数字ericonly (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.

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

#2

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

#1