CHAR funtcion with double letter columns

[Guest]

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

 

[Guest]

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

 

[MartinW]

Hi Ellen,

Maybe a better way but this should work

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

HTH
Martin

 

[Guest]

Thanks, but what is a UDF? where do I put this?

 

[Guest]

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

 

[Chip Pearson]

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)

 

[Harlan Grove]

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

 

[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