Get column number of an address in Excel

Question

In cell LJ131 I have written the text string "$HG$86" (w/ out the quotes).

I need to get the column number of the address value represented by that string, $HG$86. Everything I've tried thus far returns either an error or the column number 322 (which is the column number of column LJ).

The number I expect (if I could get to a solution) should be 215.

I can do this fine in VBA (and I'd been using that solution for a while now). But now I definitely need a function bar solution, a spreadsheet function and not VBA. However, not only is the syntax different, but I'm not sure there is a way to get at this via a function.

Thanks for any help.

Answer 1

You need to use COLUMN() and INDIRECT().

=COLUMN(INDIRECT(LJ131))

INDIRECT() translates an address (as text) into a range reference.