vba 在 Excel 中查找并删除一列数据中的某些字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/924717/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding and removing some characters in a column of data in Excel
提问by stanigator
I have copied and pasted some debugging information into an Excel sheet.
我已将一些调试信息复制并粘贴到 Excel 工作表中。
However, it contains some "weird" characters in some cells of one column, that should otherwise contain integers only. What would be the easiest way to eliminate such characters using VBA? An example is shown in the list below:
但是，它在一列的某些单元格中包含一些“奇怪”的字符，否则应该只包含整数。使用 VBA 消除此类字符的最简单方法是什么？下面的列表中显示了一个示例：
1 **'?'** <- I'm trying to get rid of the part that I have bolded
2 '?'
3 '?'
4 '?'
I want to use the file as a data source in another application. Thanks in advance.
我想将该文件用作另一个应用程序中的数据源。提前致谢。
回答by JustPlainBill
Try this (first time posting code here, so please bear with me;o). I believe I commented the VBA code enough, but any questions, just let me know.
试试这个（第一次在这里发布代码，所以请耐心等待；o）。我相信我已经对 VBA 代码进行了足够的评论，但是有任何问题，请告诉我。
' Replaces all the charaters in vaFind with strReplace
Sub FindAndReplace(ByVal vaFind As Variant, ByVal strReplace As String, ByVal rngToSearch As Range)
' vaFind is an array containing all the strings you want to replace
' strReplace is what you want to replace it with
' rngToSearch is the range you want to Find & Replace your characters
Dim x As Long
For x = LBound(vaFind, 1) To UBound(vaFind, 1)
rngToSearch.Cells.Replace What:=vaFind(x), Replacement:=strReplace, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next x
End Sub
' Now if you want to clean it automatically,
' Place the following code INTO any Sheets that you
' are have the debug data placed into.
' NOTE: prefix Asterick and question mark with a tilde to replace those characters "~*"
Private Sub Worksheet_Change(ByVal Target As Range)
' Calls the FindAndReplace sub, and removes all:
' astericks, apostrophes and "Whatever Else You need cleaned"'s
' In this case, in column A
If Not Intersect(Target, Me.Columns("A:A")) Is Nothing Then
Call FindAndReplace(Array("~*", "'", "Whatever Else You need cleaned"), "", Me.Columns("A:A"))
End If
' NOTE: This sub will be called whenever the sheet changes, and only process column A
' You can customize which columns, too.
End Sub
回答by stanigator
Here's a solution that worked for me, although it may not be very elegant:
这是一个对我有用的解决方案，尽管它可能不是很优雅：
Sub Macro1()
' Macro1 Macro
Dim temp As String
For u = 1 To 28000
If Cells(u, 4) <> 0 Then
Cells(u, 4).Select
temp = Mid(ActiveCell.Text, 1, InStr(1, ActiveCell.Text, "'") - 1)
Cells(u, 4) = temp
End If
Next
End Sub
回答by Fionnuala
I think regular expressions might be easiest. I would use an ADO recordset and look through that.
我认为正则表达式可能是最简单的。我会使用 ADO 记录集并查看它。
A few notes on RegEx
关于 RegEx 的一些注意事项
''http://msdn.microsoft.com/en-us/library/ms974570.aspx
Dim objRegEx As RegExp
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True
''This is a sample string, the field would go here
strText = "q@12""￡c"
''Find anything not a-z, 0-9
objRegEx.Pattern = "[^a-z0-9]"
''Replace with a dash, "" is fine too.
strOut = objRegEx.Replace(strText, "-")
A few notes on ADO and Excel
关于 ADO 和 Excel 的一些注意事项
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
strFile = ActiveWorkbook.FullName
strcn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& strFile & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
cn.Open strcn
rs.Open "SELECT * FROM [Sheet1$]", cn, 3 'adOpenStatic'
回答by Mesh
As you are debugging - Are you really sure you want to remove them? They are ASCII control characters. But then again i don't know what you are debugging....
在调试时 - 您确定要删除它们吗？它们是 ASCII 控制字符。但话又说回来，我不知道你在调试什么......
The characters you are seeing are unicode characters that represent the ascii control character - so wherever you have copied the data from has translated it for you.
您看到的字符是代表 ascii 控制字符的 unicode 字符 - 因此无论您从何处复制数据，都会为您翻译它。
standard excel function Clean is designed remove the ASCII control characters so wont work here.
标准的 excel 函数 Clean 旨在删除 ASCII 控制字符，因此在这里不起作用。
But this will, it removes all the unicode characters in the CONTROL PICTURES range
但这将删除控制图片范围内的所有 unicode 字符
Sub Macro1()
' Macro1 Macro
'
For u = 9210 To 9216
a = Cells.Replace(ChrW(u), "") ' replaces values in whole Worksheet
Next
End Sub
' use this to replace the values in a single column only
' i cant test this at the moment as i don't have Excel handy...
...
a = Range("A1:A2800").Replace(ChrW(u), "") ' replaces values in Col A
...
