Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
special Characters | Excel Discussion (Misc queries) | |||
Special Characters | Excel Discussion (Misc queries) | |||
Special characters | Excel Discussion (Misc queries) | |||
special characters | Excel Worksheet Functions | |||
Special characters | Excel Worksheet Functions |