A Cell Reference Custom Function

The formula to return a cell reference is quite long. This makes it an ideal candidate for a custom function.

The formula to extract a cell reference is.


The LAMBDA version is.


This is only how you test the LAMBDA function. This uses the ref parameter to capture the cell reference which is then passed to the CELL function within the SUBSTITUTE function to perform the calculation.

I have created a Custom Function called fnCELLREF to simplify this. See below.

I have started prefixing my custom functions with fn to list them together in the Name Manager and to differentiate them from standard Excel functions.

The range name dialog for this custom function is shown below.

The formula in the above dialog is.


You use the Comment section of the Range Name to create an explanation/description of the function. This appears as you are typing the name – see image below.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

3 thoughts on “A Cell Reference Custom Function

  1. I think I am missing something here.? Your LAMBDA function takes a cell reference as its argument in order to print that address in the cell as text, correct? Since you know the cell reference (you are typing it into your function), why can’t you just type that cell reference into the cell without using an equal sign in front so that it goes into the cell as text directly?

      • Ah, I see where you are going now. I guess you could also define your LAMBDA function this way too…


        I doubt there is an advantage in doing it this way, but I thought it an interesting alternative.