I have data that once extracted from comments, includes line returns. I can "Un-Wrap" but the resulting text then includes a CHAR (10) line return? I've tried to use Trim, Clean but neither has worked. I think it's a CHAR(10). Does anyone know how I can get rid of these pesky little critters? I'd like to include the code in the code I use to extract the comments.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Removing Return Characters 2
- Thread starter MoDiggity
- Start date
-
1
- #2
You could use the replace function...
[tt]
String = Replace(String, vbNewLine, ""
[/tt]
or
[tt]
String = Replace(String, Chr(10), ""
[/tt]
or
[tt]
String = Replace(String, Chr(13), ""
[/tt]
and if that is not available to you with your version of vba then it would not be hard to create one with the InStr, Left, Right, and Mid functions.
Good Luck
- Thread starter
- #3
You sure made it sound simple enough, I'm using Excel 2K, but I'm still learning the basics of programming, can you show me how I would apply it to where I have the options positioned in the following code? (the code - by the way is a huge time saver when you're data is loaded with comments)
Sub ExtractMoveComments()
Dim InputRange As Range
Set InputRange = Selection.Cells
On Error Resume Next
For Each CELL In InputRange
If CELL.Offset(0, 1).Value = "" Then
CELL.Offset(0, 1) = Mid(CELL.Comment.Text, Len _(CELL.Comment.Author) + 3)
Else
CELL.Offset(0, 1).EntireColumn.Insert
CELL.Offset(0, 1) = Mid(CELL.Comment.Text, Len _(CELL.Comment.Author) + 3)
CELL.ClearComments
CELL.Offset(0, 1).WrapText = False
'String = Replace(String, vbNewLine, ""
'String = Replace(String, Chr(10), ""
'String = Replace(String, Chr(13), ""
End If
Next CELL
End Sub
Sub ExtractMoveComments()
Dim InputRange As Range
Set InputRange = Selection.Cells
On Error Resume Next
For Each CELL In InputRange
If CELL.Offset(0, 1).Value = "" Then
CELL.Offset(0, 1) = Mid(CELL.Comment.Text, Len _(CELL.Comment.Author) + 3)
Else
CELL.Offset(0, 1).EntireColumn.Insert
CELL.Offset(0, 1) = Mid(CELL.Comment.Text, Len _(CELL.Comment.Author) + 3)
CELL.ClearComments
CELL.Offset(0, 1).WrapText = False
'String = Replace(String, vbNewLine, ""
'String = Replace(String, Chr(10), ""
'String = Replace(String, Chr(13), ""
End If
Next CELL
End Sub
-
1
- #4
Given the current state of your code, the simplest fix would be to insert this line:
anywhere before the End If, for example just after the line:
Don't be surprised if you get a few posts regarding the style of your code though.
BTW, vbNewLine is not the same as Chr$(10), it is the 2-character sequence Chr$(10)+Chr$(13) and so vb5prgrmr's first example won't work if what you have is really just the CR without the LF. (Which is the way Comments.Text appears to work.)
Code:
CELL.Offset(0, 1).Replace Chr$(10), " ", xlPart
Code:
CELL.Offset(0, 1).WrapText = FalseBTW, vbNewLine is not the same as Chr$(10), it is the 2-character sequence Chr$(10)+Chr$(13) and so vb5prgrmr's first example won't work if what you have is really just the CR without the LF. (Which is the way Comments.Text appears to work.)
- Thread starter
- #5
Since you asked, and since no one else has spoken up, here's my 2 cents worth:
There are probably as many styles of coding as there are programers, you should take the opportunity to read code from as many sources as you can find.
Generally, I like to code the routine twice. Once in native language and then again in computer code. A good programmer can always see what a routine does by reading the code, but without comments, it is not possible to know whether the routine is doing what it was intended to do. For example, your original code was inserting a new column whenever a non-blank value was found whether there was a comment to be rendered or not. That is one of the changes I made. The other was to test the .Formula property instead of the .Value property before over-writing. It is possible to have a formula that returns "" which is what is in the .Value property. In that case the formula gets over-written and the comment does not get cleared because of the way the if/else/endif is structured. That behavior is also different in my routine. However if these actions are what was intended, my code can be modified to match the behavior. Note that .Formula does not return "" unless the cell is truly empty (although it could still have formatting and its own comment, for that matter).
It is also advisable to use "Option Explicit" (without the quotes) as the first line in every module. This requires you to declare all variables which would have revealed that CELL is a variable name and not a reserved word which is what it sort of looks like when used the way you did.
Of course, if you are writing code just for yourself to use a few times and throw away, it doesn't matter whether it correctly handles all cases of data, just so long as it handles the data at hand correctly. However, if someone else will eventually need to modify your code because it is found not to work with some creative user's input, you should always keep that poor slob in mind when you make meaningful and helpful comments. (Usually that poor slob is me, and if I didn't leave good comments, I have a hard time understanding my own code six months later.)
Here is how I re-wrote the routine (I hope you find it useful):
==========================================================
There are probably as many styles of coding as there are programers, you should take the opportunity to read code from as many sources as you can find.
Generally, I like to code the routine twice. Once in native language and then again in computer code. A good programmer can always see what a routine does by reading the code, but without comments, it is not possible to know whether the routine is doing what it was intended to do. For example, your original code was inserting a new column whenever a non-blank value was found whether there was a comment to be rendered or not. That is one of the changes I made. The other was to test the .Formula property instead of the .Value property before over-writing. It is possible to have a formula that returns "" which is what is in the .Value property. In that case the formula gets over-written and the comment does not get cleared because of the way the if/else/endif is structured. That behavior is also different in my routine. However if these actions are what was intended, my code can be modified to match the behavior. Note that .Formula does not return "" unless the cell is truly empty (although it could still have formatting and its own comment, for that matter).
It is also advisable to use "Option Explicit" (without the quotes) as the first line in every module. This requires you to declare all variables which would have revealed that CELL is a variable name and not a reserved word which is what it sort of looks like when used the way you did.
Of course, if you are writing code just for yourself to use a few times and throw away, it doesn't matter whether it correctly handles all cases of data, just so long as it handles the data at hand correctly. However, if someone else will eventually need to modify your code because it is found not to work with some creative user's input, you should always keep that poor slob in mind when you make meaningful and helpful comments. (Usually that poor slob is me, and if I didn't leave good comments, I have a hard time understanding my own code six months later.)
Here is how I re-wrote the routine (I hope you find it useful):
==========================================================
Code:
Option Explicit
Sub ExtractMoveComments()
' Processes each selected cell that contains a comment
' by removing the comment and placing the text portion
' in the cell to the right with 2 leading spaces.
' Note 1: The first part of the comment (Author) is ignored.
' Note 2: If a cell to the right is not blank, then
' a new column is inserted before proceeding.
' This can lead to ragged columns if some cells
' have blank neighbors and some don't.
' Note 3: Normal Excel comment format is "John Smith:" + CR + "Hello"
' (colon ad CR without LF following the Author sub-field)
' However, it is possible to type on the author line when
' making a comment. In that case, unless the user leaves a
' space after the colon, the first character typed is lost.
Dim C As Range
Dim sComment As String
Dim a As String
For Each C In Selection
' Try to get a comment (Raises an erorr if no comment assigned.)
sComment = ""
On Error Resume Next
sComment = C.Comment.Text
If sComment <> "" Then
' Cell has a comment...
If C.Offset(0, 1).Formula <> "" Then
' R.H. cell is not blank, insert a new column
C.Offset(0, 1).EntireColumn.Insert
End If
' ...Copy text portion of the comment. Allow for ":" and first CR
C.Offset(0, 1) = Mid(sComment, Len(C.Comment.Author) + 3)
' Finish with destination cell:
C.Offset(0, 1).Replace Chr$(10), " ", xlPart ' Replace CR with blanks.
C.Offset(0, 1).WrapText = False ' Turn off word wrap.
' Finish with source cell:
C.ClearComments
End If
Next C
End Sub- Thread starter
- #7
I can easily understand the points that you've made and I'm in total agreement with you. I'm not a programmer, just a data monkey, and seldom anymore get the time to document each step of code even though it may benefit me later, especially in a in a case where I'm asking someone else to assist, but I recognize it makes more sense. I sincerely appreciate you taking the time to remind me of that.
as for the steps;
I like that you check to determine if the cell has a comment before executing the rest of the code. I was attempting that earlier today.
I had already been working with portions of the data that required a blank column to be inserted otherwise extracting the comment data would overwrite adjoining data. Even though the data contains many comments, they all fall within the same columns and so at one point I removed the insert column code entirely and just inserted columns manually before running the macro. Having it in is better, and once the first comment inserts a blank column, the offset cell will be blank for the rest of the cells in that column and the code won't re-insert additional blank columns(so no choppy looking data).
In this same data I have other cells whose content is "See Comment" or "See Comments" that can be overwritten with the comment data and don't require the inserted column so I created a second extract routine that overwrites the cells when they contain the "See Comment". If I wanted to run it from the same macro, would it be possible just to insert the following code? where?
If C.Formula = "See Comment" & "*" Then ' If cell contains "See Comment" or "See Comments"
C = Mid(sComment, Len(C.Comment.Author) + 3) 'overwrite the cell with the comment
C.Replace Chr$(10), " ", xlPart ' Replace CR with blanks.
C.WrapText = False ' Turn off word wrap.
C.ClearComments ' Delete the comment tick
as for the steps;
I like that you check to determine if the cell has a comment before executing the rest of the code. I was attempting that earlier today.
I had already been working with portions of the data that required a blank column to be inserted otherwise extracting the comment data would overwrite adjoining data. Even though the data contains many comments, they all fall within the same columns and so at one point I removed the insert column code entirely and just inserted columns manually before running the macro. Having it in is better, and once the first comment inserts a blank column, the offset cell will be blank for the rest of the cells in that column and the code won't re-insert additional blank columns(so no choppy looking data).
In this same data I have other cells whose content is "See Comment" or "See Comments" that can be overwritten with the comment data and don't require the inserted column so I created a second extract routine that overwrites the cells when they contain the "See Comment". If I wanted to run it from the same macro, would it be possible just to insert the following code? where?
If C.Formula = "See Comment" & "*" Then ' If cell contains "See Comment" or "See Comments"
C = Mid(sComment, Len(C.Comment.Author) + 3) 'overwrite the cell with the comment
C.Replace Chr$(10), " ", xlPart ' Replace CR with blanks.
C.WrapText = False ' Turn off word wrap.
C.ClearComments ' Delete the comment tick
In that case, I would modify the code to look like this:
======================================================
=====================================================
Hope that helps.
======================================================
Code:
Option Explicit
Sub ExtractMoveComments()
' Processes each selected cell that contains a comment
' by removing the comment and placing the text portion
' in the cell to the right with 2 leading spaces.
' Exception: If cell value is "See Comment" or similar,
' then the current cell is overwritten.
' Note 1: The first part of the comment (Author) is ignored.
' Note 2: If a cell to the right is not blank, then
' a new column is inserted before proceeding.
' This can lead to ragged columns if some cells
' have blank neighbors and some don't.
' Note 3: Normal Excel comment format is "John Smith:" + CR + "Hello"
' (colon ad CR without LF following the Author sub-field)
' However, it is possible to type on the author line when
' making a comment. In that case, unless the user leaves a
' space after the colon, the first character typed is lost.
Dim C As Range
Dim sComment As String
Dim nCommentCol As Integer
Dim a As String
For Each C In Selection
' Try to get a comment (Raises an erorr if no comment assigned.)
sComment = ""
On Error Resume Next
sComment = C.Comment.Text
If sComment <> "" Then
' Cell has a comment, but what about the contents...
If InStr(UCase(C.Text), "SEE COMMENT") > 0 Then
' Cell can be overwritten
nCommentCol = 0
Else
' Put comment in r.h. cell
nCommentCol = 1
End If
If ((C.Offset(0, 1).Formula <> "") And (nCommentCol = 1)) Then
' R.H. cell is not blank, insert a new column
C.Offset(0, 1).EntireColumn.Insert
End If
' ...Copy text portion of the comment. Allow for ":" and first CR
C.Offset(0, nCommentCol) = Mid(sComment, Len(C.Comment.Author) + 3)
' Finish with destination cell:
C.Offset(0, nCommentCol).Replace Chr$(10), " ", xlPart ' Replace CR with blanks.
C.Offset(0, nCommentCol).WrapText = False ' Turn off word wrap.
' Finish with source cell:
C.ClearComments
End If
Next C
End SubHope that helps.
- Thread starter
- #9
You've been a great help, and thanks for making it easy to grasp....
I added:
Application.ScreenUpdating = False (after the Dim Statements)
and
C.Offset(0, nCommentCol).HorizontalAlignment = xlLeft (after the Wraptext = False line)
I find it most amusing that there is actually less code than the comments
thanks once again....
....you don't happen to know anything about collating Access reports, do you?
thanks once again
I added:
Application.ScreenUpdating = False (after the Dim Statements)
and
C.Offset(0, nCommentCol).HorizontalAlignment = xlLeft (after the Wraptext = False line)
I find it most amusing that there is actually less code than the comments
thanks once again....
....you don't happen to know anything about collating Access reports, do you?
thanks once again
Glad to help. I'm afraid my Access knowledge is to limited to be of much help. Your best bet is to start a new thread and let someone else pick up the torch. Be sure to make it clear that your question relates to Access. Good luck.
BTW, a 1 to 1 ration of code to comments is not unusual for me, especially in short routines. Thanks for the questions, I appreciated the challenge.
One more thing, the test for "See comments" doesn't allow for the case where your user entered information along with the phrase "see comments for more detail" at the end. You may need to tighten up the test to check for that condition. Perhaps include something like "And Len(C.Text) < 15"
BTW, a 1 to 1 ration of code to comments is not unusual for me, especially in short routines. Thanks for the questions, I appreciated the challenge.
One more thing, the test for "See comments" doesn't allow for the case where your user entered information along with the phrase "see comments for more detail" at the end. You may need to tighten up the test to check for that condition. Perhaps include something like "And Len(C.Text) < 15"
- Thread starter
- #11
Yes, however it is usually a good idea to use parentheses to clarify the intent for the compiler:
Yes, it will catch "See Comments." The InStr( ) function scans a string for a substring. so if the text contains anything that looks like "xxxSee commentssssss" it will catch it.
Code:
If ((InStr(UCase(C.Text), "SEE COMMENT") > 0) And (Len(C.Text) < 15)) ThenYes, it will catch "See Comments." The InStr( ) function scans a string for a substring. so if the text contains anything that looks like "xxxSee commentssssss" it will catch it.
- Thread starter
- #13
ok,
one last challenge, I've posted this one before and never gotten a response...
I've got many macros, many custom toolbars, always changing - adding/removing buttons.
I also work on several different workstations.
In the old days... you could just copy your .xlb file and your .pst, plop them into the same locations and away you went, all buttons/macros worked like a charm...
These days however, when I copy my .pst file (can't even find the toolbar button file) all the macros get reassigned and none of the my buttons work without first re-assigning the macros. This being bad enough, I can't seem to get my current buttons/toolbars from one machine to the other...Is this so basic that I've missed it entirely? Or is it such a hassel that no one messes with it?
one last challenge, I've posted this one before and never gotten a response...
I've got many macros, many custom toolbars, always changing - adding/removing buttons.
I also work on several different workstations.
In the old days... you could just copy your .xlb file and your .pst, plop them into the same locations and away you went, all buttons/macros worked like a charm...
These days however, when I copy my .pst file (can't even find the toolbar button file) all the macros get reassigned and none of the my buttons work without first re-assigning the macros. This being bad enough, I can't seem to get my current buttons/toolbars from one machine to the other...Is this so basic that I've missed it entirely? Or is it such a hassel that no one messes with it?
I've never messed around with custom tool bars, so I can't be of much help here either.
Have you seen the O'Reilly book "Writing Excel Macros with VBA, 2nd Edition" by Steven Roman? He has a chapter on custom menus and toolbars that may have the information you seek:
Have you seen the O'Reilly book "Writing Excel Macros with VBA, 2nd Edition" by Steven Roman? He has a chapter on custom menus and toolbars that may have the information you seek:
- Thread starter
- #15
I've scoured lots of books for this particular answer, to no avail, I will take a look. I used to have code (written mostly by a MS Excel help tech), that would identify each button on each custom toolbar and apply the macro source path specified in the code to each. That, however was circa 1993-4 and I aven't been able to resurrect it since. Anyways a final thanks to you and see you another time.
MoD
MoD
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question