Deleting unwated control characters at the end in a particular column
 

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 an
take multi line input

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

As such it is not giving any problems when the user does not cut extr
blank lines. If they cut extra blank lines (say 4 blank lines) at th
end of the data there are 4 squares (control characters). I trie
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 whethe
it contain the carriage return.

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

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

Thanks in advanc

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

Deleting unwated control characters at the end in a particular column
 

Try the following function

Function RemoveTrailingLineFeeds(ByVal txt As String) As
String

Do While Right(txt, 1) = vbLf Or Right(txt, 1) = vbCr
txt = Left(txt, Len(txt) - 1)
Loop
RemoveTrailingLineFeeds = txt
End Function

Kevin Beckham

-----Original 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/

.

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/

Deleting unwated control characters at the end in a particular column
 

Hi Kevin
Have you tried Application.Worksheetfunction.Clean(yourtext)?

regards
Paul
"Kevin Beckham" wrote in message ...
Try the following function

Function RemoveTrailingLineFeeds(ByVal txt As String) As
String

Do While Right(txt, 1) = vbLf Or Right(txt, 1) = vbCr
txt = Left(txt, Len(txt) - 1)
Loop
RemoveTrailingLineFeeds = txt
End Function

Kevin Beckham

-----Original 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/

.

Deleting unwated control characters at the end in a particular column
 

Thanks Kevin, your code works fine.

The problem with CLEAN() is that it knocks off all the carriage returns
and the paragraph formatting is lost.

Thanks once again to everyone who wrote.

Another related question: If these boxes appear at the end of each and
every para (since they are carriage returns) how do I "catch" them ?

Regards


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