I found myself working inside Excel this week, doing a little bit of VBA…
It has been a while since I had to work inside this environment, my memory was stretched as I tried to remember what code I had already written (short libraries), etc. My troubles were compounded by the fact that the machine I was using did not have the VBA Help for Excel installed, so I was relying on my memory…which isn’t a good thing!
So I’m grateful for the existence of Dick’s Blog and in particular this post.
I had the need to convert a column number into it’s alphabetic counter-part…e.g. ColumnLetter(1) returns A…I was pleased to find this code snippet over at Dick’s blog:
Function ColumnLetter(ByVal colNum As Long) As String
Dim i As Long, x As Long
For i = Int(Log(CDbl(25 * (CDbl(colNum) + 1))) / Log(26)) - 1 To 0 Step -1
x = (26 ^ (i + 1) - 1) / 25 - 1
If colNum > x Then
ColumnLetter = ColumnLetter & Chr(((colNum - x - 1) \ 26 ^ i) Mod 26 + 65)
End If
Next i
End Function
It was just the ticket – thanks Dick!
When I read this the first time I thought to myself “Hah, I’ll never need that. Damn that Craig, he’s stolen 60 seconds of my life.”
Since my circumstances have changed and I needed to do exactly thins … some neurons fired, a quick search and *bamf*. I was here and my problem is solved.
This internet thing rocks!!
Thanks Craig.
Just in case someone wants this code in C#.
It is a zero based column (i.e. Zero = “A”)
public string ExcelColumnLetter(int intCol)
{
string strColumn;
char letter1, letter2;
int intFirstLetter = ((intCol) / 26);
int intSecondLetter = (intCol % 26);
intFirstLetter = intFirstLetter + 64;
intSecondLetter = intSecondLetter + 65;
if (intFirstLetter > 64)
{
letter1 = (char)intFirstLetter;
}
else
letter1 = ‘ ‘;
letter2 = (char)intSecondLetter;
strColumn = string.Concat(letter1, letter2);
return strColumn.Trim();
}
Nice work, I needed the C# version of this, could have done it myself, but you’ve saved me the effort. Thanks!
I trimmed it down a bit for my use. Maybe it’s a bit more efficient, who knows?
private string excelColumnLetter(int intCol)
{
int intFirstLetter = ((intCol) / 26) + 64;
int intSecondLetter = (intCol % 26) + 65;
char letter1 = (intFirstLetter > 64) ? (char)intFirstLetter : ‘ ‘;
return string.Concat(letter1, (char)intSecondLetter).Trim();
}
I made a little change to account for the cases where there are three lettere
public string ExcelColumnLetter(int intCol)
{
if(intCol > 16384){
throw new Exception(“Index exceeds maximum columns allowed.”);
}
string strColumn;
char letter1, letter2, FirstLetter;
int InitialLetter = ((intCol) / 676);
int intFirstLetter = ((intCol % 676) / 26);
int intSecondLetter = (intCol % 26);
InitialLetter = InitialLetter + 64;
intFirstLetter = intFirstLetter + 65;
intSecondLetter = intSecondLetter + 65;
if (InitialLetter > 64)
{
FirstLetter = (char)InitialLetter;
}
else
{
FirstLetter = ‘ ‘;
}
if (intFirstLetter > 64)
{
letter1 = (char)intFirstLetter;
}
else
{
letter1 = ‘ ‘;
}
letter2 = (char)intSecondLetter;
strColumn = FirstLetter + string.Concat(letter1, letter2);
return strColumn.Trim();
}
Real C# Conversion:
public static string ColumnLetter(int colNum)
{
string s = “”;
for (int i = Convert.ToInt32(Math.Log(Convert.ToDouble(25 * (Convert.ToDouble(colNum) + 1))) / Math.Log(26)) – 1; i >= 0; i–)
{
int x = Convert.ToInt32(Math.Pow(26, i + 1) – 1) / 25 – 1;
if (colNum > x)
{
s += (char)(((colNum – x – 1) / Convert.ToInt32(Math.Pow(26, i))) % 26 + 65);
}
}
return s;
}
if you don’t want to use VB, the following will return the current column letter:
IF(COLUMN()<=26,CHAR(96+COLUMN()),(CHAR(96+(ROUNDDOWN((COLUMN()-1)/26,0)))&CHAR(96+IF(MOD(COLUMN(),26)=0,26,MOD(COLUMN(),26)))))
WOOHOO!!! Just what the doctor ordered. Ended up changing it a little but this was pretty much all done and ready to plug in. Thanks guys!
Just wanted to say thanks for posting this. It came in handy for me today.
please can anyone tell me what if i want the opposite of this function i want to convert the letters to numbers…how can i do that?
thanks in advance
any help is appreciated
dim strCol as string
dim intCol as integer
strCol = “K”
intCol = Columns(strCol).Column
Yeah.. thanks a lot, i’ve used it and it rocks!
public string ExcelColumnLetter(int intCol)
Above mentioned function is really good one.
It is James Owen’s comment
just it require “-1” for number of columns
thx
ADAM! Thank you very much.
In spanish and uppercase:
=SI(COLUMNA()<=26;CARACTER(64+COLUMNA());(CARACTER(64+REDONDEAR.MENOS((COLUMNA()-1)/26;0)))&CARACTER(64+SI(RESIDUO(COLUMNA();26)=0;26;RESIDUO(COLUMNA();26))))
I modified some of the examplecode above, so this is what I ended up with.
It works for columns with 3 letters and it’s written in vba.
Column A = 1, B = 2 and so on.
Function fnColumnNumberToLetter(ByVal lColNum As Long) As String
If (lColNum > Columns.Count) Then
MsgBox (“Index exceeds maximum columns allowed.”)
Exit Function
End If
Dim strColumn As String
Dim sLetter1 As String
Dim sLetter2 As String
Dim sFirstLetter As String
Dim iInitialLetter As Integer
Dim iFirstLetter As Integer
Dim iSecondLetter As Integer
‘Three letters?
If (lColNum > 702) Then
iInitialLetter = Int((lColNum – 703) / 676)
iInitialLetter = iInitialLetter + 65
sFirstLetter = Chr(iInitialLetter)
Else
sFirstLetter = “”
End If
‘Second letter?
iFirstLetter = Int(((lColNum – 1 – 26) Mod 676) / 26)
iFirstLetter = iFirstLetter + 65
If (iFirstLetter > 64) Then
sLetter1 = Chr(iFirstLetter)
Else
sLetter1 = “”
End If
‘This letter always exists!
iSecondLetter = (lColNum Mod 26)
If (iSecondLetter = 0) Then
iSecondLetter = 26
End If
iSecondLetter = iSecondLetter + 64
sLetter2 = Chr(iSecondLetter)
‘Puts togehter the result…
strColumn = Trim(sFirstLetter & sLetter1 & sLetter2)
fnColumnNumberToLetter = strColumn
End Function
umm… 3 lines of code for A – ZZZZZZZZZ…Z?
public static string NumToLetter(int Col)
{
if (Col <= 26) return ((char)(Col + 64)).ToString();
//puts us on Zero Bound Index… tis where my math is 1337.
Col–;
return NumToLetter(Col / 26) + NumToLetter((Col % 26) + 1);
}
MattEvan16’s Code takes 15ms to generate NumToLetter references for 1 – 16384 (max excel 2007 columns)
Not bad! And such a small amount of code too! Makes you wonder why everyone else was overthinking it
This is how a Beginner like me solved this issue (there are better ways to do it, f.e. using function)
‘capture ColumnID
Dim ColumnID As String
Dim TempColumnAddress As String
Dim TempColumnID As String
TempColumnAddress = ActiveCell.Address
TempColumnID = TempColumnAddress
TempColumnID = Left(TempColumnID, 3) ‘$AA or $A$
TempColumnID = Right(TempColumnID, 1) ‘Letter or $
If TempColumnID = “$” Then
ColumnID = Left(TempColumnAddress, 2)
ColumnID = Right(ColumnID, 1)
Else
ColumnID = Left(TempColumnAddress, 3)
ColumnID = Right(ColumnID, 2)
End If
I have a spreadsheet where i need to count the number of instances of a certain text within a row however i only have the column numbers not column letters like a countif function.
Can anyone help?
how to convert over 2147483647
This is not as concise as MattEvans16’s C code, but for those who need it in VBA, here is a recursive function that works for any number of letters in the column address…
Function ColNumToAlpha(ColumnNumber As Integer) As String
ColNum = ColumnNumber
ColLetters = “”
Do
ColLetters = Chr(((ColNum – 1) Mod 26) + 65) & ColLetters
ColNum = Int((ColNum – ((ColNum – 1) Mod 26)) / 26)
Loop While ColNum > 0
ColumnNumToAlpha = ColLetters
End Function
Ooops – typo in the function name on the return. Also declaring the variables in this version…
Function ColNumToAlpha(ColumnNumber As Integer) As String
Dim ColNum as Integer
Dim ColLetters as String
ColNum = ColumnNumber
ColLetters = “”
Do
ColLetters = Chr(((ColNum – 1) Mod 26) + 65) & ColLetters
ColNum = Int((ColNum – ((ColNum – 1) Mod 26)) / 26)
Loop While ColNum > 0
ColNumToAlpha = ColLetters
End Function
Here is a quick test program for the VB code. Just comment in/out the different test cases:
Sub TestColNumToLetter()
Dim TestNum As Integer
‘ TestNum = 25 ‘Y
‘ TestNum = 270 ‘JJ
‘ TestNum = 703 ‘AAA
TestNum = 16343 ‘XDO
MsgBox “Column Address in Alpha: ” & ColNumToAlpha(TestNum), vbOKOnly, “Column Number: ” & TestNum
End Sub
@INCOGNITO: If you want to convert numbers beyond the integer range, just convert the variable type to double. I’m not sure why you would want to do that since even Excel 2007 is limited to 16,384 columns (XFD), but I tested it with my VB code and 2147483647 returned “FXSHRXX”. I’ll let you do the math to verify that’s correct.
Here is the quick way:
Split(Columns(intColumnNumber).Address, “:”)(0)
Pour ceux qui en auraient besoin en Pascal (Delphi):
function ColNumToAlpha(ColumnNumber: integer): string;
var
ColNum : integer;
ColLetters : string;
begin
ColNum := ColumnNumber;
ColLetters := ”;
while (ColNum > 0) do begin
ColLetters := Chr(((ColNum – 1) mod 26) + 65) + ColLetters;
ColNum := (ColNum – ((ColNum – 1) mod 26)) div 26;
end;
Result := ColLetters;
end;
/* PHP VERSION COLUMN NUMBER TO EXCEL COLUMN NAME */
/* THANKS TO MattEvans16 he did the algorithm above*/
/* Useful for php / mysql excel export using phpexcel from codeplex */
function NumToLetter($Col)
{
if ($Col <= 26) return chr($Col + 64);
//puts us on Zero Bound Index… tis where my math is 1337.
$Col–;
return NumToLetter($Col / 26) . NumToLetter(($Col % 26) + 1);
}
//cold fusion version
function NumToLetter(Col){
Col = int(Col);
if (Col <= 26) return ToString(Chr(Col + 64));
//puts us on Zero Bound Index… tis where my math is 1337.
Col = (Col – 1);
return NumToLetter(Col / 26) + NumToLetter((Col MOD 26) + 1);
}
MattEvans16:
“puts us on Zero Bound Index… tis where my math is 1337.”
What does that even mean?
And why does everyone keep repeating that?
@Brad
zero bound index … instead of starting a counter at 1 and incrementing to to whatever, he starts his counter from 0 (in lots of computer stuff starting from 0 is very useful).
1337 … upsidedown, backwards, and read as letters, it’s LEET which, people who speak 1337, know means “elite”.
Why does everyone repeat that … because they are paying homage to MattEvans16’s total awesomeness.
My direct translation of MattEvans16’s (your math is indeed 133t) C code into VBA:
Public Function NumToLetter(iCol As Integer) As String
If (iCol <= 26) Then
NumToLetter = Chr(iCol + 64)
Else
iCol = iCol – 1
NumToLetter = NumToLetter(iCol / 26) + NumToLetter((iCol Mod 26) + 1)
End If
End Function
Works perfect!! Thanks
To Marcus Hansson,
Thanks man. That is exactly what I needed.
public int getPower(int num, int power) {
if (num == 0) return 1;
if (getInterval(power) > num) return power;
else return getPower(num, power + 1);
}
public int getInterval(int power) {
return (int) (Math.pow(26, power + 1) – 26) / 25;
}
public char get2Ascii(int intColumn) {
int intAscii = 65;
intAscii += intColumn;
return (char) intAscii;
}
public String get2Alphabet(int number) {
String alphabet = “”;
int asciiCode = 0;
int power = getPower(number, 0);
int prePower = power – 1;
int preInterval = getInterval(prePower);
int interval = number – preInterval;
while (power > 0) {
asciiCode = (int) (interval / Math.pow(26, power – 1));
alphabet = alphabet + get2Ascii(asciiCode);
interval = interval – (int) Math.pow(26, power – 1) * asciiCode;
power–;
}
return alphabet;
}
Yikes! A lot of calculations there. I wouldn’t expect that to be very performant. I’ve written code both to convert from an integer to a spreadsheet column label, and to convert the column label back to an integer. I’ve posted it at http://www.blackbeltcoder.com/Articles/strings/converting-between-integers-and-spreadsheet-column-labels.
Here is another short recursive version
Public Function GetHeaders(N As Integer) As String
Dim theNewVal As Integer, theCurLetter As String
theNewVal = (N – 1) \ 26
thelowletterval = (N – 1) Mod 26
theCurLetter = Chr(thelowletterval + 65)
If theNewVal > 0 Then
theCurLetter = GetHeaders(theNewVal) & theCurLetter
End If
GetHeaders = theCurLetter
End Function
Adam, thanks for the formula! (Gabriel, you may be able to use this, too). i prefer that to VBA code most of the time. Anyway, one simplification in the last part. There is no need for an “IF” there: IF(COLUMN()<=26,CHAR(96+COLUMN()),(CHAR(96+(ROUNDDOWN((COLUMN()-1)/26,0)))&CHAR(97+MOD(COLUMN()-1,26))))).
With due credit to the original solution, here is an even simpler one, about a 30% reduction in length:
IF(COLUMN()<=26,CHAR(96+COLUMN()),CHAR(96+INT((COLUMN()-1)/26))&CHAR(97+MOD(COLUMN()-1,26)))
Even easier
Dim myColumn As String
myColumn = Replace(Cells(1, maxcol).Address(0, 0), “1”, “”)
[…] Excel: Column Number to Letter(s)Oct 28, 2005 … I found myself working inside Excel this week, doing a little bit of VBA… …. (( COLUMN()-1)/26,0)))&CHAR(96+IF(MOD(COLUMN(),26)=0,26 … […]
Hi All
At MSN site it gives vb code (for Excel Macros) that only works to around 450 columns, after that it starts to become in accurate due to division. I tried these above but can’t convert accurately to run in a Macro in Excel – can someone enlighten me please! For example I have columns past VQ (no. 589).
=LEFT(SUBSTITUTE(CELL(“address”,OFFSET(A1,0,A1-1)),”$”,””),FIND(“$”,CELL(“address”,OFFSET(A1,0,A1-1)),2)-2)
I would LOVE to do the opposite. IE
static int GetColumIndexFromHeader(string header)
{
}
so that if I call
GetColumIndexFromHeader(“A”) = 0
GetColumIndexFromHeader(“Z”) = 25
GetColumIndexFromHeader(“ABA”) = ???
PLEASE HELP
I translated MattEvans16′s into VB (.Net in my case) and there is one difference that I want to emphasize: need to use ‘\’ NOT ‘/’. ‘\’ is integer devision and is need for this code to work ALL the time.
I used JJ’s test values and 16343 came out XEO.
These are my test values (all of the once above 1000 will fail without ‘\’:
retVal = NumToLetter(1) ‘A
Debug.Print(retVal)
retVal = NumToLetter(25) ‘Y
Debug.Print(retVal)
retVal = NumToLetter(270) ‘JJ
Debug.Print(retVal)
retVal = NumToLetter(703) ‘AAA
Debug.Print(retVal)
retVal = NumToLetter(1007) ‘ALS
Debug.Print(retVal)
retVal = NumToLetter(6774) ‘IZN
Debug.Print(retVal)
retVal = NumToLetter(16343) ‘XDO
Once again thanks MattEvans16 – I love recursion, especially when it works well.
This is my VB.Net code:
Public Function NumToLetter(ByVal colNumber As Long) As String
If colNumber <= 26 Then
'Debug.Print(Chr(colNumber + 64))
Return Chr(colNumber + 64)
End If
colNumber = colNumber – 1
NumToLetter = NumToLetter(colNumber \ 26) + NumToLetter((colNumber Mod 26) + 1)
End Function