如何从字符串中删除所有非字母数字字符,除了excel中的句点和空格? [英] 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
只需将此子放在同一个模块中并执行它。请注意,这将取代范围内的任何公式。
Simply place this sub in the same module and execute it. Be aware though, that this will replace any formulas in the range.
这篇关于如何从字符串中删除所有非字母数字字符,除了excel中的句点和空格?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!