Numbers to Alphabets

[rk0909]

Hello,

I want to create a formula where the input is a number representing a
column (e.g. 20) but i want that to be interpretted as an alphabet
corresponding
to the numbers (T in this case). Is there a conversion formula that
exists?

thanks,

RK

 

[Bob Phillips]

=LEFT(ADDRESS(1,20,2),FIND("$",ADDRESS(1,20,2),1)-1)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Marcelo]

hi
=char(a1+64)

assuming the a1 has the 20

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"rk0909" escreveu:

 

[Gary''s Student]

=SUBSTITUTE((LEFT(ADDRESS(1,A1),3)),"$","")

so if A1 contains 26, the formula returns Z
so if A1 contains 27, the formula returns AA
etc.

 

[Steve Yandl]

If the number is input at A1
=CHAR(64+A1)

Steve Yandl

 

[rk0909]

thanks much works perfect. Also is there a way to do the vice versa.
code(cell)-64 works only A to Z and not for AA onwards.

thanks much.

 

[Dave Peterson]

Another one:
=SUBSTITUTE(ADDRESS(1,A1,2),"$1","")

Hello,

I want to create a formula where the input is a number representing a
column (e.g. 20) but i want that to be interpretted as an alphabet
corresponding
to the numbers (T in this case). Is there a conversion formula that
exists?

thanks,

RK

 

[Gord Dibben]

You could use a couple of UDF's

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

=getcolnum("ax") returns 50

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

=getcollet(186) returns GD


Gord Dibben MS Excel MVP

 

[Dave Peterson]

one way:
=COLUMN(INDIRECT(A1&"1"))

thanks much works perfect. Also is there a way to do the vice versa.
code(cell)-64 works only A to Z and not for AA onwards.

thanks much.

 

[Gary''s Student]

=IF(LEN(A1)=1,CODE(A1)-64,(26*(CODE(LEFT(A1,1))-64))+CODE(RIGHT(A1,1))-64)

so if A1 contains Z the formula returns 26
so if A1 contains AA the formula returns 27
so if A1 contains IV the formula returns 256

 

[Roger Govier]

Hi

The following will work with numbers from 1 to 256 (A to IV)
=SUBSTITUTE(CHAR(INT(A1/26)+64)&CHAR(MOD(A1,26)+64),"@","")