I opened a text-file in Excel and some of the cells contain one to eight
characters that look like squares. I want to delete the squares so the
cell is empty.
Using "Find and Replace", what do I put in the:
. "Find What" box?
. "Replace With" box?
Hi Gary,
To establish what the offending character is, in an empty cell insert
the formula:
=CODE(A20)
where A20 represents one of the problematic cells.
Then:
Alt-F11 to open the VBA editor
Alt-IM to insert a new code module
At the flashing cursor, paste the following code:
'=============>>
Option Explicit
'------------->>
Public Sub ReplaceChr()
Dim rCell As Range
Dim Rng As Range
Const ReplaceChr As Long = 9 '<<===== Replace 9 with the value
returned by the =CODE(A20)
formula
Application.ScreenUpdating = False
On Error Resume Next
Set Rng = Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not Rng Is Nothing Then
For Each rCell In Rng.Cells
rCell.Replace what:=Chr(ReplaceChr), _
Replacement:="", _
LookAt:=xlPart
Next rCell
End If
Application.ScreenUpdating = False
End Sub
'<<=============
Alt-Q to close the VBA editor and return to Excel
With the problematic worksheet selected:
Alt-F8 to open the Macro Window | select Tester | Run | Ok
As an alternative method of establishing the contents of any of the
problematic cells, you could download Chip Pearson's very useful
CellView Add-In. This add-in will identify each character in the
active cell and return its code, including hidden characters of
which you may be unaware. For a fuller explanation and to download
the add-in, visit:
http://www.cpearson.com/Excel/CellView.aspx
===
Regards,
Norman
