1

How to find the char of number of a column? COLUMN function can only obtain the char of column, but I want to obtain like the 27-column is AA, since in the $INDIRECT$ function, if I want to use the abstract value like INDIRECT($A$1) I have to use the char instead of R1C1 form.

A.Oreo
  • 301
  • 3
  • 16
  • Possible duplicate of [Convert cells(1,1) into "A1" and vice versa](http://stackoverflow.com/questions/6262743/convert-cells1-1-into-a1-and-vice-versa), although only if you are using VBA. – Ken Y-N Mar 27 '17 at 03:28

1 Answers1

0

I think you are looking for the ADDRESS() function, if you are using cell functions rather than VBA programming. On my Excel install:

=ADDRESS(1,1)

Gives $A1$1.

=ADDRESS(1,1,1,0)

Gives R1C1.

=ADDRESS(1,1,1,1)

Gives $A1$1.

I must have different defaults than you, but the fourth parameter will switch between R1C1 and A1 styles.

Now, if you just want the column letter, one way is to delete the row text, for instance:

=SUBSTITUTE(ADDRESS(1,27,4,1),"1","")

This gives AA. ADDRESS(1,27,4,1) returns AA1 - note that I set the third parameter to 4 to say to return relative (no $) addresses, then SUBSTITUTE() replaces the 1 (the row number we passed in to ADDRESS) with the empty string.

Ken Y-N
  • 14,644
  • 21
  • 71
  • 114