Deleting unwated control characters at the end in a particular column

[ssexcel]

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

 

[Kevin Beckham]

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

 

[BrianB]

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
================================================

 

[Paul Robinson]

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

regards
Paul

 

[ssexcel]

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