Need to fetch comment text in an Excel cell so you can filter, count or report on it? Excel has no built-in formula that reads a comment, but a five-line VBA user-defined function (UDF) does the job. In this tutorial you build Get_Comments, a UDF that returns the comment of any cell as normal text and strips the user name Excel adds at the top. The example is an attendance sheet where absent and leave codes carry comments, and one formula pulls every reason into its own column.
⬇️ Download the Practice File
Get-Comments.zip · contains Get Comments.xlsm (macro-enabled) · enable macros after opening
Watch the Video Tutorial
PK writes the function from scratch in the Visual Basic Editor, tests the RIGHT, LEN and FIND logic on the sheet first, and then moves the same logic into VBA to remove the user name and the extra line break.
What You Will Build
- A custom worksheet function
=Get_Comments(cell)that returns the comment text of a cell. - Automatic clean-up of the UserName: line that Excel puts at the start of a new comment.
- A blank result for cells without a comment, using IFERROR.
- A comments column next to your data that you can filter, count or pivot.
The Practice Data
The workbook has one sheet with an attendance log in A1:F11: Date, EMP Id, Name, Supervisor, Attendance and Fetch Comments. The Attendance column holds the codes P (present), A (absent) and L (leave). Several A and L cells carry a comment with the reason, for example No Information., Going home for sister’s marriage, Personal reason and Birthday Leave. The goal is to bring those reasons into column F.
Step 1 – Open the VBA Editor and Insert a Module
Press Alt+F11, or go to Developer → Visual Basic. In the Visual Basic Editor choose Insert → Module. A UDF must live in a standard module (not in a sheet module) so that worksheet formulas can see it.
Step 2 – Write the UDF to Fetch Comment Text
Paste this code into the module. It is the same code used in the practice file (Module1):
Option Explicit
Function Get_Comments(rng As Range) As String
Application.Volatile
Get_Comments = rng.Comment.Text
'Remove user name
If InStr(Get_Comments, ":" & Chr(10)) > 0 Then
Get_Comments = Right(Get_Comments, Len(Get_Comments) - VBA.InStr(Get_Comments, ":" & Chr(10)) - 1)
End If
End FunctionHow the code works
Function Get_Comments(rng As Range) As String– the function takes one cell and returns text. The name you give the function is the name you type in the formula.Application.Volatile– makes the function recalculate whenever Excel recalculates, so the result refreshes after you change a comment and press F9.rng.Comment.Text– reads the full text of the cell’s comment. If the cell has no comment, this line fails and the cell shows #VALUE!, which IFERROR handles in Step 3.InStr(..., ":" & Chr(10))– looks for a colon followed by a line break.Chr(10)is the line feed, the character you get when you press Enter inside a comment. InStr returns its position, or 0 if it is not there.Right(text, Len(text) - position - 1)– keeps only the characters after the colon and the line break, which removes the user name.
Why colon plus line break?
When you insert a classic comment, Excel starts it with your user name, a colon and a new line, for example Priyendra.Kumar: followed by Birthday Leave. In the video PK checks the numbers on the sheet first: the whole text is 31 characters long and the colon sits at character 16. Taking the right 31 – 16 = 15 characters gives the comment, but with the line break still at the front. Subtracting one more character (the - 1) drops that line break, so the result is exactly the 14 characters of Birthday Leave.
Searching for the colon and the line break together, rather than a colon alone, also protects you when the comment itself contains a colon, such as Reason: medical. Only the colon at the end of the user name line is followed by a line break.
Step 3 – Use the Function on the Sheet
Back in Excel, enter this in F2 and fill it down to F11:
=IFERROR(Get_Comments(E2),"")Cells with a comment now show the reason, and cells without one stay blank instead of showing #VALUE!. In the practice file you can see No Information., Going home for sister’s marriage, Personal reason and Birthday Leave appear next to the matching codes.
If you edit a comment later, press F9 or use Formulas → Calculate Now. Editing a comment does not change any cell value, so Excel does not recalculate on its own.
Save as a macro-enabled workbook
Save the file as Excel Macro-Enabled Workbook (.xlsm). A normal .xlsx file cannot keep VBA code, and the formulas would turn into #NAME? the next time you open it.
About the second function in the file
The practice workbook also contains Fetch_Comments in Module2, which is the version PK types during the video, and column G uses it. It finds the position with a plain colon search. Both give the same results on this data, but Get_Comments is the safer one because it looks for the colon and line break together.
Comments vs Notes in Modern Excel
This section is an addition and is not covered in the 2019 video. In Microsoft 365, Excel has two different features:
- Notes – the classic yellow comment boxes shown in this tutorial. They are now created with Review → Notes → New Note (or Shift+F2). In VBA they are still the
Commentobject, soGet_Commentsworks with them unchanged. - Threaded comments – the newer conversation-style comments with replies, created with Review → New Comment. In VBA they belong to
Range.CommentThreaded. Reading them throughrng.Comment.Textdoes not give you the conversation itself, so the original UDF is not the right tool for them.
If your workbook may contain both, this extended version (also an addition) checks for a threaded comment first and falls back to a note:
Function Get_Comment_Any(rng As Range) As String
Application.Volatile
Dim txt As String
If Not rng.CommentThreaded Is Nothing Then
txt = rng.CommentThreaded.Text
ElseIf Not rng.Comment Is Nothing Then
txt = rng.Comment.Text
If InStr(txt, ":" & Chr(10)) > 0 Then
txt = Mid(txt, InStr(txt, ":" & Chr(10)) + 2)
End If
End If
Get_Comment_Any = txt
End FunctionIt returns the first message of a threaded comment (replies are in CommentThreaded.Replies) and an empty string when there is no comment at all, so IFERROR is not needed. CommentThreaded only exists in Excel versions that support threaded comments; in older versions use the original Get_Comments. Mid(txt, position + 2) is simply a shorter way of writing the same Right and Len calculation.
Common Errors and Fixes
#NAME? instead of the comment
Excel cannot find the function. Check that macros are enabled, that the code is in a standard module of the same workbook, and that the file is saved as .xlsm. If the code is in another workbook or add-in, call it with the workbook name in front.
#VALUE! on some rows
Those cells have no comment. Wrap the function in IFERROR as shown in Step 3, or use the extended version, which returns an empty string.
The result does not change after editing a comment
Comment edits do not trigger a recalculation. Press F9 or Formulas → Calculate Now.
The user name is still there
The comment probably has no line break after the colon, for example because the user name was typed on the same line. Edit the comment so the text starts on a new line, or adjust the search in the code.
Tips
- Use
Paste Special → Valueson the comments column once you are done, so the text stays even if the comments are deleted. - Combine the column with COUNTIF or a pivot table to count absences by reason.
- Store the UDF in your Personal Macro Workbook if you want it in every file.
- Read Microsoft’s explanation of the difference between threaded comments and notes and the Range.CommentThreaded property.
Want It Ready-Made?
If you track attendance every day, the Employee Attendance Data Entry System in Excel on NextGenTemplates.com gives you a ready-made entry form and records, and you can browse more HR and payroll Excel templates there.
Related Tutorials
- Excel VBA Course for Beginners: Free 18-Chapter Tutorial
- Insert a Picture in Excel Comments
- UDF to Evaluate a Formula in Excel
- Count Sub-strings in an Excel Cell with a VBA UDF
- Attendance Tracker in Excel: Monthly Sheet Step by Step
Frequently Asked Questions
Is there a built-in Excel formula to get comment text?
No. Excel has no worksheet function that reads comments or notes, which is why a small VBA user-defined function such as Get_Comments is used.
How do I remove the user name from the comment text?
The UDF looks for a colon followed by a line break, which is how Excel ends the user name line, and keeps only the text after it. See Step 2 for the code.
Why does the formula show #VALUE! for some cells?
Those cells have no comment, so the code cannot read one. Wrap the function in IFERROR with an empty result, as shown in Step 3.
Does this work with threaded comments in Microsoft 365?
The original UDF reads Notes, the classic comments. For threaded comments use the extended version in the Comments vs Notes section, which reads Range.CommentThreaded first.
Do I need to save the file as .xlsm?
Yes. VBA code is only kept in macro-enabled formats such as .xlsm or .xlsb. In an .xlsx file the function is removed and the formulas return #NAME?.
About the Author
PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.
Conclusion
A tiny UDF is all you need to fetch comment text in an Excel cell: read rng.Comment.Text, cut everything up to the colon and line break, and wrap the call in IFERROR. Once the reasons sit in a normal column you can filter, count and report on them like any other data. Download the practice file, try it on your own attendance sheet, and use the extended version if your team works with threaded comments.
