Column Name Formula
 

This should be an easy one:

Is there a function that returns the name of the column (A,B,C,....) just
like there is one for row, but returns the static value of the column (A,B,C)
and not just a column number??

I can do it by setting up a table and associating 1 with A, 2 with B, and so
on...but that's anoying especially if I am expecting to go past the 26 letter
alphabet into the combinations.

Thanks for any help!!!

Column Name Formula
 

The CELL function can return the address of a cell.

=CELL("address",A1)

This returns the value: $A$1

You could manipulate this result to show just the column letter.

=MID(CELL("address",A1),2,FIND("$",CELL("address", A1),2)-2)

This would just return: A

HTH,
Elkar


"masterbaker" wrote:

This should be an easy one:

Is there a function that returns the name of the column (A,B,C,....) just
like there is one for row, but returns the static value of the column (A,B,C)
and not just a column number??

I can do it by setting up a table and associating 1 with A, 2 with B, and so
on...but that's anoying especially if I am expecting to go past the 26 letter
alphabet into the combinations.

Thanks for any help!!!

Column Name Formula
 

This UDF will give you the column letter.

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber 26))
End Function

=getcollet(167) returns FK

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Enter the formula as shown above.


Gord Dibben Excel MVP


On Tue, 25 Jul 2006 14:41:02 -0700, masterbaker
wrote:

This should be an easy one:

Is there a function that returns the name of the column (A,B,C,....) just
like there is one for row, but returns the static value of the column (A,B,C)
and not just a column number??

I can do it by setting up a table and associating 1 with A, 2 with B, and so
on...but that's anoying especially if I am expecting to go past the 26 letter
alphabet into the combinations.

Thanks for any help!!!

Gord Dibben MS Excel MVP