Substituting for special characters

[JPyle]

I have read many of the posts here but do not find a solution for my issue.
I am importing a CSV file that is generated by my construction estimating
software. Many carriage returns and spaces in the original are converted to a
special character (a box with a question mark in it)in Excel. I need to
remove these characters and replace with a space.

If I use the Clean or Trim function I lose the formatting. Essentially I
want to keep the formatting and get rid of the special character.

 

[Gary''s Student]

Run the following macro:

Sub cleanum()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
v = r.Value
For i = 1 To 31
v = Replace(v, Chr(i), "")
Next
For i = 128 To 255
v = Replace(v, Chr(i), "")
Next
r.Value = v
Next
End Sub

 

[JPyle]

This almost works. I changed the null substitution to a single space but I am
loosing my carriage returns. Any way to keep those?

 

[Dave Peterson]

chr(10) is the linefeed character:

Sub cleanum()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
v = r.Value
For i = 1 To 31
if i = 10 then
'skip it
else
v = Replace(v, Chr(i), "")
end if
Next
For i = 128 To 255
v = Replace(v, Chr(i), "")
Next
r.Value = v
Next
End Sub

 

[JPyle]

Using the advice of the two posts, here is the macro that seems to work:

Sub cleanum()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
v = r.Value
For i = 1 To 9
v = Replace(v, Chr(i), " ")
Next
For i = 11 To 31
v = Replace(v, Chr(i), " ")
Next
For i = 128 To 255
v = Replace(v, Chr(i), " ")
Next
r.Value = v
Next
End Sub