Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
What you are asking is a bit confusing to understand. Can you reword it so it is easier to understand? – Jay Jan 9 '12 at 16:10
up vote 7 down vote accepted

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

=COLUMN(INDIRECT(LJ131))

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

share|improve this answer
    
Haw! Thanks Excellll! – M M Jan 9 '12 at 17:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .