Extract Comments Embedded in the N() Function

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.
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?

Well, of course, Dobrin. Let me think about it for a minute.

1. I can use the MID and FIND function to extract any part of a cell. Easy.
2. I can use conditional formatting with a formula. Easy.
3. 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 text

The 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).

1. In Excel, press ALT+F11 to get into the VBA window.
2. Click Insert and then Module to insert a new module sheet to the workbook.
3. Paste the following code into the module

Here’s the code you can copy and paste:

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 Function

Here 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.  

A formula to read the comment within N()

Assuming the formula is in A2 as per the above example, I can now combine some FIND, LEN and MID formula trickery to read the portion that is at the end of the formula. I entered this formula in Column C. =MID(formulatext(A2),FIND(“N(“,formulatext(A2))+3,LEN(formulatext(A2))-FIND(“N(“,formulatext(A2))-4) Confused? Copy the formula into the spreadsheet and run it step-by-step using Evaluate Formula in Formula  > Auditing Tools. This is what it looks like in Column C. Not bad, eh? There’s a #VALUE! error displayed for row 4 because there is no N() in that row. You might want to enclose the whole formula within IFERROR to take care of that.

Conditional Formatting to Identify Formulas with Comments

Notice how that first column shows in blue all cells which contain the N() function? I used conditional formatting with a formula to highlight them that way. Here’s the formula for A2, just make sure that you apply that rule to the whole range.

What’s your way?

A little knowledge of text functions, Google search and User-defined formulas (see Excel VBA Training) goes a long way. Excel is all about creatively combining various simple techniques to produce extraordinary results. Was this the only way? No. Was it the best way? Maybe not. It was the first that popped into my head when I read Dobrin’s comment to Comment Excel formulas with the N() function. What’s your solution.