在Excel中查找和删除一列数据中的某些字符
问题描述:
我已经将一些调试信息复制并粘贴到Excel工作表中。在Excel中查找和删除一列数据中的某些字符
但是,它在一列的某些单元格中包含一些“怪异”字符,否则该字符应仅包含整数。使用VBA消除这些字符最简单的方法是什么?一个例子如下所示:
1 **'␁'** <- I'm trying to get rid of the part that I have bolded
2 '␂'
3 '␃'
4 '␂'
我想在另一个应用程序中使用该文件作为数据源。提前致谢。
答
这里是为我工作的解决方案,虽然它可能不是很优雅:
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
答
我认为正则表达式可能是最简单的。我会使用ADO记录集并查看它。
对正则表达式
''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 = "[email protected]""£c"
''Find anything not a-z, 0-9
objRegEx.Pattern = "[^a-z0-9]"
''Replace with a dash, "" is fine too.
strOut = objRegEx.Replace(strText, "-")
一些注意的几个注意事项对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'
+0
为什么请倒票? – Fionnuala 2009-05-29 10:18:58
答
当你正在调试 - 难道你真的确定要删除它们?它们是ASCII控制字符。但是接下来我不知道你在调试什么....
你看到的字符是代表ascii控制字符的unicode字符 - 所以无论你从哪里复制数据,都已经为你翻译过。
standard excel函数Clean设计去除了ASCII控制字符,所以不会在这里工作。
但这种意志,它在控制图像删除所有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
...
答
尝试这(第一次在这里发帖的代码,所以请忍受我; 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
@stanigator:你应该以一种不会让别人知道你的问题的方式来提问。我试图重新制定它,以便更容易理解。如果我没有达到要求,请纠正它。 – Tomalak 2009-05-29 07:16:11
完成;编辑做得很好。必须感谢您使措辞更清晰。我添加了一块我想删除的示例字符。 – stanigator 2009-05-29 08:03:13