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.