How to convert comments to cell contents in Excel?
Supposing I have a range cells filled with comments in a worksheet, and now, I want to convert all of the comments to cell contents so that I can print them neatly and roundly. Are there any good ways to solve this task?
Recommended Productivity Tools for Excel/Office
By using this method, you need to create a defined function first, and then apply the defined formula to convert the comments to cell contents. You can finish it as the following steps:
1.Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following function into the Module:
Function GetComments(pRng As Range) As String 'Updateby20140509 If Not pRng.Comment Is Nothing Then GetComments = pRng.Comment.Text End If End Function
2.Press Ctrl + S to save the function.
3.Then input the following formula “=GetComments(A1 )” in any blank cell, in this case, I will use cell E1. See screenshot:
4. Press the Enter key. And select cell E1, then drag the fill handle over the range of cells that you want to contain this formula. And all of the comments are converted to cell contents. See screenshot:
If you know the VBA code, the following brief code also can help you to convert comments to cell contents.
1. Select the range that you want to convert to cell contents;
2. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and input the following code into the Module:
Sub CommentToCell() Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) For Each Rng In WorkRng Rng.Value = Rng.NoteText Next End Sub
3. Then click button to run the code. And select a range you want to convert then click OK, and all of the selected comments have been converted to cell contents in its relevant cells.
Note: If you don’t want the comments, you can delete them.
The Convert Comment and Cell of Kutools for Excel is a handy and useful tool can help you to solve many problems about comments.
After installing Kutools for Excel, please do as follows:
1. Select the range that you want to import the comments into the cells.
2. Click Kutools > More > Convert Comment and Cell, see screenshot:
3. In the Convert Comment and Cell dialog box, select Convert comments into content of cells. See screenshot:
4. Then click OK. Now all of the selected comments are converted to cell contents in its original range.
For more detailed information about Convert Comment and Cell, please visit Convert Comment and Cell feature description.
- Change all comment formats in cells
- Change all comments' author name
- List all comments to a new worksheet or workbook
- Find and replace text within comments
- Show or hide all comments and comment indicators in Excel
- Convert cell contents to comments in Excel
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months agois there a way to do this without the title of a comment or the non bold writing?
To post as a guest, your comment is unpublished.· 11 months agoGreat tip, thanks for sharing!
To post as a guest, your comment is unpublished.· 1 years agoPerfect! Thank You
To post as a guest, your comment is unpublished.· 1 years agoYou are a star!!! thanks
To post as a guest, your comment is unpublished.· 1 years agoIt does not automatically update when I change the comment. How do I change this?