Replacing characters
 

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

Replacing characters
 

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

---
Regards,
Norman



"Phil Floyd" wrote in message
...
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

Replacing characters
 

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

"Norman Jones" wrote in message
...
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

---
Regards,
Norman



"Phil Floyd" wrote in message
...
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