Excel: Finding and removing some characters in a column of data

Question

I have copied and pasted some debugging information into an Excel sheet.

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:

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.

Answer 1

I think regular expressions might be easiest. I would use an ADO recordset and look through that.

A few notes on 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

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'

Answer 2

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

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.

standard excel function Clean is designed remove the ASCII control characters so wont work here.

But this will, it removes all the unicode characters in the CONTROL PICTURES range

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

Answer 3

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

Answer 4

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.

' 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