Threaded comment errors

[Gigiwan]

My excel file comments are bugged after one of my co-workers used ubuntu and saved it, now every comment on my excel file show
"
[Threaded comment]

Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more"

How do i get rid of all these notes without losing the original comment?

 

[bbrnx19]

try

Sub RemoveCommentText()
Set a = ActiveWorkbook
' look at each sheet in workbook
For Each x In a.Sheets
    'look at each comment in sheet
    For Each y In x.Comments
        ' comment text
        b = y.Shape.DrawingObject.Caption
        ' split comment text by "
        c = Split(b, Chr(34))
        d = "[Threaded comment]"
        nc = ""
        
        For Each i In c
            e = Right(Left(i, InStr(i, "]")), Len(d))
            If e = d Then
            Else
                 nc = nc + i
            End If
        Next
        y.Shape.DrawingObject.Caption = nc
    Next
Next
End Sub

 

[Gigiwan]

try

Sub RemoveCommentText()
Set a = ActiveWorkbook
' look at each sheet in workbook
For Each x In a.Sheets
    'look at each comment in sheet
    For Each y In x.Comments
        ' comment text
        b = y.Shape.DrawingObject.Caption
        ' split comment text by "
        c = Split(b, Chr(34))
        d = "[Threaded comment]"
        nc = ""
     
        For Each i In c
            e = Right(Left(i, InStr(i, "]")), Len(d))
            If e = d Then
            Else
                 nc = nc + i
            End If
        Next
        y.Shape.DrawingObject.Caption = nc
    Next
Next
End Sub

I tried it, but all the contents within the comment section is deleted as shown below:

All i need is to get rid of all the text highlighted in yellow instead

 

[bbrnx19]

Ok hope the " where in the comment
Looks like highlighted text is at start of the comment each time
then following will remove nr= number of characters from start of comment string , adjust nr as need

Sub RemoveCommentText()
Set a = ActiveWorkbook
' look at each sheet in workbook
For Each x In a.Sheets
    'look at each comment in sheet
    For Each y In x.Comments
        ' comment text
        b = y.Shape.DrawingObject.Caption
        ' nr of characters from start of comment
        ' to be remove include for vbLf
        
        nr = 34 ' revise as needed
        nc = Right(b, Len(b) - nr)
        
        y.Shape.DrawingObject.Caption = nc
    Next
Next
End Sub

 

[bbrnx19]

Previous post #4 wrong use following

Sub RemoveCommentText()
Set a = ActiveWorkbook
' look at each sheet in workbook
For Each x In a.Sheets
    'look at each comment in sheet
    For Each y In x.Comments
        ' read comment text frist
        b = y.Shape.DrawingObject.Caption
        If Left(b, 1) = "[" Then
            'thread comment cell address
            c = y.Parent.Cells.Address
            '  delete new styled thread comment 2018 -----!
            ' an create a new old styled comment 
            Range(c).ClearComments
            Range(c).AddComment
            ' nr of characters from start of comment
            ' to be remove including for vbLf
            nr = 237
            Range(c).Comment.Text Text:=Right(b, Len(b) - nr)
        Else
        End If
    Next
Next
End Sub

 

[Gigiwan]

Previous post #4 wrong use following

Sub RemoveCommentText()
Set a = ActiveWorkbook
' look at each sheet in workbook
For Each x In a.Sheets
    'look at each comment in sheet
    For Each y In x.Comments
        ' read comment text frist
        b = y.Shape.DrawingObject.Caption
        If Left(b, 1) = "[" Then
            'thread comment cell address
            c = y.Parent.Cells.Address
            '  delete new styled thread comment 2018 -----!
            ' an create a new old styled comment
            Range(c).ClearComments
            Range(c).AddComment
            ' nr of characters from start of comment
            ' to be remove including for vbLf
            nr = 237
            Range(c).Comment.Text Text:=Right(b, Len(b) - nr)
        Else
        End If
    Next
Next
End Sub

I tried this one but my comment turned from this

to this

and when i excuted the vba code,i got that error

 

[bbrnx19]

change

b = y.Shape.DrawingObject.Caption

with

b = y.Text

when you press Debug which line of the code is highlighted

 

[Gigiwan]

when you press Debug which line of the code is highlighted

Range(c).Comment.Text Text:=Right(b, Len(b) - nr)

 

[bbrnx19]

replace

            Range(c).AddComment
            ' nr of characters from start of comment
            ' to be remove including for vbLf
            nr = 237
            Range(c).Comment.Text Text:=Right(b, Len(b) - nr)

with

            nr = 237
            Range(c).AddComment Right(b, Len(b) - nr)