Not so long ago, I showed you how to insert comments using the N() function in Excel formulas. But can you get the comment out of that cell using a function, reader Dobrin asks?
Thank you for this article – the N() funtion is very usefull and I use it often in my work.Well, of course, Dobrin. Let me think about it for a minute.
Anyway, I have two unsolved issues with it, see if you can help:
– Is it possible to automatically highlight (e.g. with conditional formatting) those cells, which contain this function?
– Since I have hundrets of cells with the N() fuction, is there a way extract as text in other cells the comments, embeded in the function?
- I can use the MID and FIND function to extract any part of a cell. Easy.
- I can use conditional formatting with a formula. Easy.
- For the two points above, I need to apply the functions to the formula text rather than the formula result – which is what Excel tends to do.
A user-defined function to read the formula textThe first part of the solution is to create a user-defined function to read the formula text then the rest will be straightforward. Let’s create this new function and call it FormulaText (a Google search can help you find some code, or you could install Morefunc by Laurent Longre to extend Excel and find the related function).
- In Excel, press ALT+F11 to get into the VBA window.
- Click Insert and then Module to insert a new module sheet to the workbook.
- Paste the following code into the module
Function FormulaText(r As Range) As String ' Taken from Gary''s student ' http://www.excelforum.com/excel-formulas-and-functions/533793-return-a-formula-as-text-string-to-a-cell.html FormulaText = Chr(39) & r.Formula End FunctionHere is an example. In Column A, I entered some formulas including some comments in N(). In column B, I use the FormulaText user-defined function to display the formula as a string. And this is what it looks like in Excel.