Current location - Quotes Website - Signature design - As shown in the picture, how can EXCEL extract all the comments in a row into the remarks column corresponding to the date above, and delete the author's signature of the comments? I have been looking
As shown in the picture, how can EXCEL extract all the comments in a row into the remarks column corresponding to the date above, and delete the author's signature of the comments? I have been looking
As shown in the picture, how can EXCEL extract all the comments in a row into the remarks column corresponding to the date above, and delete the author's signature of the comments? I have been looking for it for a long time and I don't understand?

There is no quick and easy way to extract it as imagined.

It can be done with a small piece of code. Press the Alt+F11 key combination to open the VBE window, execute "Insert" "Module", copy the following code into it, and make sure the worksheet is the active worksheet, click "Run" - "Run Subprocess" to get result.

Note: If you find a red error message when pasting, just delete the blank lines between the statements.

Sub Demo()

Dim Orng As Range

Dim Drng As Range

Dim Cell As Range

Dim i As Integer

With ActiveSheet

Set Orng = .UsedRange.Find("A")

Set Drng = .UsedRange.Find("Remarks ")

For Each Cell In .Range(Orng, .Cells(Rows.Count, Orng.Column).End(xlUp))

.Cells(Cell.Row, Drng .Column).ClearContents

For i = Cell.Column + 1 To Drng.Column - 1

If Not .Cells(Cell.Row, i).Comment Is Nothing Then< /p>

.Cells(Cell.Row, Drng.Column) = .Cells(Cell.Row, Drng.Column) & .Cells(Drng.Row, i) & "日" & _

< p> """ & Application.Clean(Replace(.Cells(Cell.Row, i).Comment.Text, .Cells(Cell.Row, i).Comment.Author & ":", "")) & " "" _

& vbCrLf

End If

Next i

Next

End With

End Sub

Run results: