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

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

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

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

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

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

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","")

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","")