View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
BrianB BrianB is offline
external usenet poster
 
Posts: 109
Default Deleting unwated control characters at the end in a particular column

You could use the =CLEAN() worksheet function to remove these
characters.
eg. in another column put =CLEAN(a1) and copy down. Then select the
column, Copy, Edit/Paste Special/Values, OK. Delete the original
column.

Regards
BrianB
================================================


ssexcel wrote in message ...
Greetings all !

I have used a user form to take userinput. The user "cut and paste"
data from another window. The text box is formatted to warp text and
take multi line input

While saving in excel the Carriage returns are showing as control
characters.

As such it is not giving any problems when the user does not cut extra
blank lines. If they cut extra blank lines (say 4 blank lines) at the
end of the data there are 4 squares (control characters). I tried
doing a find and replace and excel is not finding this character.

I need solution for this problem:

I want to remove the extra line feeds/carraige returns at the end
automatically.

1. Is there a function by which I can do it ?

2. How to read the last few characters in that cell and check whether
it contain the carriage return.

3. I cannot do a global find and replace in that cell because the
other carriage return also would get replaced and all the line spacing
would be lost.

4. How to loop to check all the cells in that column ?

Thanks in advance


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/