Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CHAR funtcion with double letter columns
I am using this function:
CHAR(MATCH(P$1,$1:$1,)+64) to get teh column letter, however it does not count double letters past column z. As in AA AB etc. Is there a way to get this? Ellen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CHAR funtcion with double letter columns
Try this UDF
Function CLetter(CNumber As Integer) As String CLetter = Left(Cells(1, CNumber).Address _ (False, False), 1 - (CNumber 26)) End Function call it with =cletter(n) Mike "ellebelle" wrote: I am using this function: CHAR(MATCH(P$1,$1:$1,)+64) to get teh column letter, however it does not count double letters past column z. As in AA AB etc. Is there a way to get this? Ellen |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CHAR funtcion with double letter columns
Hi Ellen,
Maybe a better way but this should work =LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN() ,4))-1) HTH Martin "ellebelle" wrote in message ... I am using this function: CHAR(MATCH(P$1,$1:$1,)+64) to get teh column letter, however it does not count double letters past column z. As in AA AB etc. Is there a way to get this? Ellen |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CHAR funtcion with double letter columns
Thanks, but what is a UDF? where do I put this?
"Mike H" wrote: Try this UDF Function CLetter(CNumber As Integer) As String CLetter = Left(Cells(1, CNumber).Address _ (False, False), 1 - (CNumber 26)) End Function call it with =cletter(n) Mike "ellebelle" wrote: I am using this function: CHAR(MATCH(P$1,$1:$1,)+64) to get teh column letter, however it does not count double letters past column z. As in AA AB etc. Is there a way to get this? Ellen |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CHAR funtcion with double letter columns
ellebelle,
Martin's solution does it wothout a UDF but for future reference it's a User Defined Function. To use it copy it and then Alt+F11. Right click on 'this workbook' and insert module Paste the code in there. In any cell enter the formula =cletter(n) where n is the number of the column you want. Mike "ellebelle" wrote: Thanks, but what is a UDF? where do I put this? "Mike H" wrote: Try this UDF Function CLetter(CNumber As Integer) As String CLetter = Left(Cells(1, CNumber).Address _ (False, False), 1 - (CNumber 26)) End Function call it with =cletter(n) Mike "ellebelle" wrote: I am using this function: CHAR(MATCH(P$1,$1:$1,)+64) to get teh column letter, however it does not count double letters past column z. As in AA AB etc. Is there a way to get this? Ellen |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CHAR funtcion with double letter columns
Function CLetter(CNumber As Integer) As String
CLetter = Left(Cells(1, CNumber).Address _ (False, False), 1 - (CNumber 26)) End Function For compatibility with Excel 2007 which has 16384 columns (out to "XFD"), use Function CLetter(CNumber As Integer) As String CLetter = Left(Cells(1, CNumber).Address _ (False, False), 1 - (CNumber 26) - (CNumber 702)) End Function -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Mike H" wrote in message ... Try this UDF Function CLetter(CNumber As Integer) As String CLetter = Left(Cells(1, CNumber).Address _ (False, False), 1 - (CNumber 26)) End Function call it with =cletter(n) Mike "ellebelle" wrote: I am using this function: CHAR(MATCH(P$1,$1:$1,)+64) to get teh column letter, however it does not count double letters past column z. As in AA AB etc. Is there a way to get this? Ellen |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CHAR funtcion with double letter columns
"MartinW" wrote...
Maybe a better way but this should work =LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN( ),4))-1) .... Could be shorter. =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CHAR funtcion with double letter columns
Hi Harlan,
I used to count lateral thinking as one of my talents. These newsgroups soon knocked that nonsense out of my head. <g Regards Martin "Harlan Grove" wrote in message ... "MartinW" wrote... Maybe a better way but this should work =LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN (),4))-1) ... Could be shorter. =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double quotes when concatenating with CHAR(10) | Excel Discussion (Misc queries) | |||
How do I format a letter in Excell with double strikethrough N? | Excel Discussion (Misc queries) | |||
8500 cells with phone number(7 char.), wishing to add area code (10 char.) | Excel Discussion (Misc queries) | |||
Double Stacked Columns - can it be done? | Excel Discussion (Misc queries) | |||
How do I split 16 char text 'XX XXX= +. X' into 16 columns | Excel Discussion (Misc queries) |