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

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.

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

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?

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

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

/* 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);
}

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

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

[…] 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).

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

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