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

data-cleaningexcelvba

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.

Best Solution

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    
Related Question