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.

Run the following macro:

Sub cleanum()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
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
--
Gary''s Student - gsnu200771


"JPyle" wrote:

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.

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

"Gary''s Student" wrote:

Run the following macro:

Sub cleanum()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
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
--
Gary''s Student - gsnu200771


"JPyle" wrote:

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.

chr(10) is the linefeed character:

Sub cleanum()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
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 wrote:

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

"Gary''s Student" wrote:

Run the following macro:

Sub cleanum()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
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
--
Gary''s Student - gsnu200771


"JPyle" wrote:

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.




--

Dave Peterson

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

Sub cleanum()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants)
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


"JPyle" wrote:

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.