Replacing characters

[Phil Floyd]

I have exported data from a database that looks like this in excel. .

BLN B172-ZNste
tc122

I can't figure out how to take out the squares because I don't know what
they are. The file was exported to a .csv and this is what I get. Text to
column doesn't work nor does Find/Replace. What are these characters and
how can I eliminate or replace them.

Phil

 

[Norman Jones]

Hi Phil,

Try the following:

Public Sub ReplaceChr()

Dim rCell As Range
Dim rng As Range
Const ReplaceChr As Long = 29 '

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

rCell.Replace what:=Chr(ReplaceChr), _
Replacement:="", _
LookAt:=xlPart
Next
End If

Application.ScreenUpdating = False

End Sub

 

[Phil Floyd]

Thanks Norman. You just saved me a ton of work.
Phil