Numbers to Alphabets

R

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
 
B

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

Marcelo

hi
=char(a1+64)

assuming the a1 has the 20

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



"rk0909" escreveu:
 
G

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.
 
R

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.
 
D

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
Click to expand...
 
G

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
 
D

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.
Click to expand...
 
G

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
 
R

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