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

YasserKhalil

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Jaafar Tribak

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

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

Active Member
Joined
Jul 11, 2018
Messages
412
Office Version
  1. 365
Platform
  1. Windows
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
CD
1Sample Text
2For 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.
3For 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.
4Sample Comment
5Dan 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
RangeFormula
D2D2=ShowFormattingSigns(C2,TRUE)
D3D3=ShowFormattingSigns(C3,FALSE)


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

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
412
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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
 
Solution

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
412
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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

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

Active Member
Joined
Jul 11, 2018
Messages
412
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,689
Messages
5,637,837
Members
416,985
Latest member
mrindira

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