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/

.