Convert column number to letter

frustrated123

New Member
Joined
Jan 13, 2004
Messages
9
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.
 
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
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
I use this:
Code:
=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.

Code:
=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:

Code:
=SUBSTITUTE(ADDRESS(1,(C3),4),"1","") = Q
But if I needed to go to AA, then I can't get that one.
 
Upvote 0
I use this:
Code:
=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.

Code:
=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:

Code:
=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?
 
Upvote 0
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).
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top