reveal formatting in comment or notes...

sadams1

Board Regular
Joined
Aug 19, 2006
Messages
139
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!
 

Attachments

  • Untitled.png
    Untitled.png
    71.8 KB · Views: 12
  • Untitled1.png
    Untitled1.png
    33.6 KB · Views: 12

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

sadams1

Board Regular
Joined
Aug 19, 2006
Messages
139
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

Board Regular
Joined
Aug 19, 2006
Messages
139
Question for a moderator...if no interest is shown, should the question be restated?
 

sadams1

Board Regular
Joined
Aug 19, 2006
Messages
139

ADVERTISEMENT

should this be restated?
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,203
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Do you mean a UDF like this :
VBA Code:
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

Well-known Member
Joined
Jun 24, 2010
Messages
817
@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

Well-known Member
Joined
Dec 5, 2002
Messages
8,203
Office Version
  1. 2016
Platform
  1. Windows
@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

Well-known Member
Joined
Dec 5, 2002
Messages
8,203
Office Version
  1. 2016
Platform
  1. Windows
here is the UDF with the Optional IncludeCommentAuthor argument:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,593
Members
417,154
Latest member
gm_jagath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top