Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing certain characters in a cell | Excel Worksheet Functions | |||
Replacing ascii characters | Excel Worksheet Functions | |||
Replacing specific characters | Excel Discussion (Misc queries) | |||
Replacing characters | Excel Worksheet Functions | |||
Replacing characters with leading zeroes | Excel Programming |