Square character(s) in cells

G

gary

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?
 
N

Norman Jones

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
 
N

Norman Jones

On 03/02/2014 02:04, Norman Jones wrote:
[cut]
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
[cut]

Hi Gary,

the above instructions should have read:

Alt-Q to close the VBA editor and return to Excel
With the problematic worksheet selected:
Alt-F8 to open the Macro Window | select ReplaceChr | Run | Ok


===
Regards,
Norman
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top