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

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



"rk0909" wrote in message
...
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

hi
=char(a1+64)

assuming the a1 has the 20

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



"rk0909" escreveu:

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

=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.
--
Gary''s Student - gsnu200765


"rk0909" wrote:

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

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

Steve Yandl



"rk0909" wrote in message
...
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

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" wrote:

=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.
--
Gary''s Student - gsnu200765


"rk0909" wrote:

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

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

rk0909 wrote:

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

--

Dave Peterson

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

On Fri, 11 Jan 2008 14:26:01 -0800, rk0909
wrote:

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" wrote:

=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.
--
Gary''s Student - gsnu200765


"rk0909" wrote:

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

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

rk0909 wrote:

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" wrote:

=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.
--
Gary''s Student - gsnu200765


"rk0909" wrote:

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


--

Dave Peterson

=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
--
Gary''s Student - gsnu200765


"rk0909" wrote:

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" wrote:

=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.
--
Gary''s Student - gsnu200765


"rk0909" wrote:

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

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

--

Regards
Roger Govier

"rk0909" wrote in message
...
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" wrote:

=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.
--
Gary''s Student - gsnu200765


"rk0909" wrote:

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