reveal formatting in comment or notes...

[sadams1]

Not sure how to best describe this in words so have attached 2 screenshots of what I'd like to see if is possible.

In MS Word, (1st screenshot) a string of text is entered & the codes are showing...the "spaces" & "return" characters are visible. It counts 14 characters...the "return" isn't counted.

In Excel (2nd screenshot) the same string of text is entered into a Note & then the characters are counted with a cool function a moderator here helped me troubleshoot. It counts 14 characters as well.

Question is, is it possible to show or reveal the formatting of a Note like can be done in Word? In other words, when counting characters, I'd like to be able to see if a character that isn't visible is a "space". Is there an editor like "vi" for Notes or Comments? Maybe something in VBA?
Thanks!

 

[sadams1]

This will probably be considered a "bump" but is attaching screenshot more effective than actual files? For this thread I can resubmit with files if it would be more useful.

 

[sadams1]

Question for a moderator...if no interest is shown, should the question be restated?

 

[sadams1]

bump

 

[sadams1]

should this be restated?

 

[sadams1]

at what point do I wave the red flag in defeat?

 

[Jaafar Tribak]

Do you mean a UDF like this :

Function CharacterCountInComment(ByVal Cell As Range) As Variant

    Dim oComment As Comment, sCmText As String, lSpacesCount As Long
    
    If Cell.Cells.Count > 1 Then CharacterCountInComment = CVErr(xlErrNA): Exit Function
    On Error Resume Next
        Set oComment = Cell.Comment
        If oComment Is Nothing Then CharacterCountInComment = CVErr(xlErrNA): Exit Function
    On Error GoTo 0
    sCmText = Mid(Cell.Comment.Text, InStr(Cell.Comment.Text, vbLf) + 1, Len(Cell.Comment.Text))
    lSpacesCount = Len(sCmText) - Len(WorksheetFunction.Substitute(sCmText, " ", ""))
    CharacterCountInComment = "Character Count " & Len(sCmText) & " (with " & lSpacesCount & " spaces)"

End Function

 

[YasserKhalil]

@JAAFAR
Great function. I have tested it and put a comment with two lines and the UDF returns only the second line length and returns one space only while there were two spaces.

 

[Jaafar Tribak]

@JAAFAR
Great function. I have tested it and put a comment with two lines and the UDF returns only the second line length and returns one space only while there were two spaces.

That's because the UDF considers the first line as (Comment Author) ie: not part of the text.

Perhaps adding an optional Boolean argument to the UDF (Include Author) is needed.

Thanks YasserKhalil for the feedback.

 

[Jaafar Tribak]

here is the UDF with the Optional IncludeCommentAuthor argument:

Function CharacterCountInComment(ByVal Cell As Range, Optional ByVal IncludeCommentAuthor As Boolean) As Variant

    Dim oComment As Comment, sCmText As String, lSpacesCount As Long
   
    If Cell.Cells.Count > 1 Then CharacterCountInComment = CVErr(xlErrNA): Exit Function
   
    On Error Resume Next
        Set oComment = Cell.Comment
        If oComment Is Nothing Then CharacterCountInComment = CVErr(xlErrNA): Exit Function
    On Error GoTo 0
   
    With Cell.Comment
        If IncludeCommentAuthor Or InStr(.Text, .Author & ":" & vbLf) = 0 Then
            sCmText = .Text
        Else
            sCmText = Replace(.Text, .Author & ":" & vbLf, "")
        End If
    End With
   
    lSpacesCount = Len(sCmText) - Len(WorksheetFunction.Substitute(sCmText, " ", ""))
    CharacterCountInComment = "Character Count " & Len(sCmText) & " (with " & lSpacesCount & " spaces)"

End Function