Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Substituting for special characters

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.



  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default Substituting for special characters

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.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Substituting for special characters

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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Substituting for special characters

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Substituting for special characters

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.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
special Characters P. Zicari Excel Discussion (Misc queries) 2 October 22nd 07 04:09 PM
Special Characters aftamath Excel Discussion (Misc queries) 1 October 18th 05 11:02 PM
Special characters Matthew S Excel Discussion (Misc queries) 10 September 24th 05 12:36 AM
special characters tjh Excel Worksheet Functions 3 May 13th 05 10:50 PM
Special characters Gilles Desjardins Excel Worksheet Functions 2 December 8th 04 04:17 AM


All times are GMT +1. The time now is 06:04 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"