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!

 

[YasserKhalil]

Thank you very much
I tried to use both True and False and I got the same result 34 Characters and two spaces. I expect two different outputs when using False and True!!

 

[Jaafar Tribak]

Thank you very much
I tried to use both True and False and I got the same result 34 Characters and two spaces. I expect two different outputs when using False and True!!

Yes. That's because there is no Author string + ":"+ line feed, hence, the UDF considers the entire text.
In other words, if the comment displays the default bold string Author: accross the top, then the boolean argument is taken into account.

That's the logic I followed. Maybe someone else has a better idea.

 

[sadams1]

Looks good & thank you for checking this out. Do you know if there's a way to see the formatting of the comments in VBA?

 

[Dan_W]

Just to clarify, are you referring to the nonprinting formatting marks - like dot space • or the Pilcrow sign ¶ ?

If so, I might be wrong, but I don't think that this function is built into Excel. You will see it in Word and Outlook (and perhaps also Publisher?), but that's because that software comprises word processing functionality. Strictly speaking, Excel isn't designed for that.

In terms of being able to add that functionality by way of VBA, I've adjusted the code above that Jaafar helpfully provided and it has produced the following results:

Book1
	C	D
1	Sample Text
2	For Excel fans, the biggest disappointment with Excel 2007 SmartArt diagrams is that their text is static. You cannot have the text for a SmartArt diagram dynamically calculated by Excel.	Dan•W:¶As•••a•workaround,•••you•can•use•the•SmartArt••••tools•to•build•a•diagram•and•then•convert•the•••diagram•to•shapes.•¶¶You•can•then•apply•formulas•to•the••••shapes.
3	For Excel fans, the biggest disappointment with Excel 2007 SmartArt diagrams is that their text is static. You cannot have the text for a SmartArt diagram dynamically calculated by Excel.	For•Excel•fans,•the•biggest•disappointment•with•Excel•2007•SmartArt•diagrams•is•that•their•text•is•static.•You•cannot•have•the•text•for•a•SmartArt•diagram•dynamically•calculated•by•Excel.
4	Sample Comment
5	Dan W: As a workaround, you can use the SmartArt tools to build a diagram and then convert the diagram to shapes. You can then apply formulas to the shapes.
Sheet1

Cell Formulas
Range	Formula
D2	D2	=ShowFormattingSigns(C2,TRUE)
D3	D3	=ShowFormattingSigns(C3,FALSE)

I will follow up with the code., but is this broadly what you were after?

 

[Dan_W]

Just to explain the above, C2 and C3 are identical in terms of the text in the cell and the comment in both cells. I have reproduced the comment in C5. D2 and D3 show the operation of the function, but D2 is set to produce the formatting signs for the text in the comment (if any), whereas D3 pulls the text from the cell itself. By default, the text is sourced from the comment.

The relevant code for the function is below. I just realised that I have accidentally removed the error handling code, so apologies, but I thought I ought to get this to you sooner rather than later.

Endt With

End Witth

Function ShowFormattingSigns(ByVal Target As Range, Optional ByVal IncludeComment As Boolean = True) As String
    
    If IncludeComment Then
        
        Dim oComment As Comment, Source As String
        
        On Error Resume Next
        
            Set oComment = Target.Comment
            
            If oComment Is Nothing Then Exit Function
        
        On Error GoTo 0
    
        Source = oComment.Text

    Else
    
        Source = Target.Value
    
    End If
    
    ShowFormattingSigns = WorksheetFunction.Substitute(WorksheetFunction.Substitute(Source, Chr(10), "¶"), Chr(32), Chr(149))

End Function

 

[Dan_W]

One thing I did want to point out is that it can be quite difficult to read the text when the signs and the text are the same colour. The following subroutine will 'correct' that by recolouring the signs as a light grey:

Sub FixColour(Target As Range)
    
    Dim i As Long, CurrentChar As String, LightGrey As Long
    
    LightGrey = rgb(200, 200, 200)

    For i = 1 To Len(Target.Value)
        
        CurrentChar = Mid(Target.Value, i, 1)
        
        If CurrentChar = "•" Or CurrentChar = "¶" Then Target.Characters(i, 1).Font.Color = LightGrey
    
    Next
    
End Sub

Note that this will only work where the cell with the text to be recoloured actually contains the text, and not a formula.

 

[sadams1]

hello & many thanks for messing with this. I'll check it out & wrestle with it as I'm just a hack & it takes me awhile for get something like this going. Quick side note, this looks like what I'd wanting to do but I swear I've seen something in VBA that shows the measurements of the attributes of a comment/note as well as what's inside it much like the "ruler" in Word. Or I could just be smoking too much crack!

 

[Dan_W]

So it may be me who is smoking too much of the 'aforementioned', but you originally asked "is it possible to show or reveal the formatting of a Note" said "I'd like to be able to see if a character that isn't visible is a "space"", but now you're looking for measurements of the attributes of a comment/note.... what does that even mean? I'm easily confused, so I probably misread something :-/

If ever come across this VBA code you reference above, please do let me know - I'm curious to see what it looks like.