CHAR funtcion with double letter columns

E

ellebelle

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
 
M

Mike H

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
 
M

MartinW

Hi Ellen,

Maybe a better way but this should work

=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN(),4))-1)

HTH
Martin
 
M

Mike H

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
 
C

Chip Pearson

Function CLetter(CNumber As Integer) As String
CLetter = Left(Cells(1, CNumber).Address _
(False, False), 1 - (CNumber > 26))
End Function
Click to expand...

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)
 
H

Harlan Grove

MartinW said:
Maybe a better way but this should work

=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN(),4))-1)
Click to expand...
....

Could be shorter.

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
 
M

MartinW

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
 
Top