Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
how can i multiply two columns | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions |