Convert column number to letter

[frustrated123]

I can obtain the columns numbers but I cannot get the letters. Is there anyway to convert from a number to a letter?
eg. somefunction(1) gives me column(A) as an answer?

Thanks.

 

[galileogali]

A barroque solution that I armed in the past. A species of algorithm. ( valid Excel 2003,,,,Excel2007 with adaptations)
=IF(COLUMN()<=26,"",IF(MOD(COLUMN(),26)=0,CHAR((INT(COLUMN()/26)-1)+64),CHAR(INT(COLUMN()/26)+64)))&CHAR(IF(MOD(COLUMN(),26)=0,26+64,MOD(COLUMN(),26)+64))

GALILEOGALI

 

[mellen]

hi!
good only for column A to Z

=CHAR(64+COLUMN(A2))

In relation to this, what is the constant (64 in this case) if we wanted to convert the column number beyond z?

Thanks,

 

[RoryA]

It won't work for that - you should use Aladin's method. The formula relies on the fact that there is only one column letter and the ASCII character codes for A-Z are 65-90 (so you add 64 to column 1 to get A and so on).

 

[thelogicbox]

Function columnNumber2Letter(columnNumber As Long) As String

Dim columnAddress As String

If columnNumber > 0 And columnNumber <= Columns.Count Then
columnAddress = Columns(columnNumber).Address
columnNumber2Letter = Mid(columnAddress, 2, InStr(2, columnAddress, ":") - 2)
End If

End Function

 

[patbarb]

And if you want a less elegant, but much easier to support solution, how about a Vlookup table? Can then simply lookup the column# and bring back the correct letter.

LOOKUP TABLE
-------
1 A
2 B
3 C
4 D
etc.
<code>
=Vlookup('column#','Lookup table',2,False)
</code>
patrick

 

[chavalikumar]

I can obtain the columns numbers but I cannot get the letters. Is there anyway to convert from a number to a letter?
eg. somefunction(1) gives me column(A) as an answer?

Thanks.

Here is a solution:
"=MID(ADDRESS(ROW(),COLUMN()),2,FIND("$",ADDRESS(ROW(),COLUMN()),2)-2)"

Cheers!

 

[Rowland Hamilton]

I use this:

=IF(COLUMN()<=26,"",IF(MOD(COLUMN(),26)=0,CHAR((INT(COLUMN()/26)-1)+64),CHAR(INT(COLUMN()/26)+64)))&CHAR(IF(MOD(COLUMN(),26)=0,26+64,MOD(COLUMN(),26)+64))

and get "@" in front of the answer. I am referencing a cell value for the column number. i.e.

=IF($C3<=26,"",IF(MOD($C3,26)=0,CHAR((INT($C3/26)-1)+64),CHAR(INT($C3/26)+64)))&CHAR(IF(MOD($C3,26)=0,26+64,MOD($C3,26)+64)) = @Q

Cell value = 17 whic is Row Qso really only need this to get correct answer:

=SUBSTITUTE(ADDRESS(1,(C3),4),"1","") = Q

But if I needed to go to AA, then I can't get that one.

 

[Aladin Akyurek]

I use this:

=IF(COLUMN()<=26,"",IF(MOD(COLUMN(),26)=0,CHAR((INT(COLUMN()/26)-1)+64),CHAR(INT(COLUMN()/26)+64)))&CHAR(IF(MOD(COLUMN(),26)=0,26+64,MOD(COLUMN(),26)+64))

and get "@" in front of the answer. I am referencing a cell value for the column number. i.e.

=IF($C3<=26,"",IF(MOD($C3,26)=0,CHAR((INT($C3/26)-1)+64),CHAR(INT($C3/26)+64)))&CHAR(IF(MOD($C3,26)=0,26+64,MOD($C3,26)+64)) = @Q

Cell value = 17 whic is Row Qso really only need this to get correct answer:

=SUBSTITUTE(ADDRESS(1,(C3),4),"1","") = Q

But if I needed to go to AA, then I can't get that one.

Doesn't post #2 in this thread help to solve your problem? If it does not help, care to state the problem in words?

 

[ksamiot]

I know it has been years, but I came across this problem only now. Here's what worked for me:

=IF(COLUMN()>26;CHAR(INT(COLUMN()/26)+64);"")&CHAR(IF(MOD(COLUMN();26)=0;26;MOD(COLUMN();26))+64)

The above will produce an alphabetic prefix of one-letter for every column >26. And then just add a single letter representing the column number divided by 26. All start from character 65 (A).

 

[ksamiot]

I know it has been years, but I came across this problem only now. Here's what worked for me:

=IF(COLUMN()>26;CHAR(INT(COLUMN()/26)+64);"")&CHAR(IF(MOD(COLUMN();26)=0;26;MOD(COLUMN();26))+64)

The above will produce an alphabetic prefix of one-letter for every column >26. And then just add a single letter representing the column number divided by 26. All start from character 65 (A).

Sorry. The above formula doesn't work quite as expected. Here's the remedy:

=IF(COLUMN()>26;CHAR(INT((COLUMN()-1)/26)+64);"")&CHAR(IF(MOD(COLUMN();26)=0;26;MOD(COLUMN();26))+64)