Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Viewing Control Characters | Excel Discussion (Misc queries) | |||
Deleting Characters | Excel Discussion (Misc queries) | |||
Deleting rows with 11+ characters. | Excel Worksheet Functions | |||
Deleting rows with 11+ characters. | Excel Discussion (Misc queries) | |||
deleting check control box | Excel Discussion (Misc queries) |