Excel - Excel columns: Converting numbers to letters and vice versa

Asked By Jack

23-Mar-08 04:07 PM

Hello,
My app is using Excel spreadsheet.
User has a choice of preselecting some of the sheet's columns.
Now:
Excel is using letters as the visual  indexing, of the columns but when
programming the corresponding number (A -->1, B -->2..., AA-->28, AB-->29
etc) must be used..

What will be the best method of converting those letters into numbers?
Jack

23-Mar-08 04:13 PM

As usual I have made a mistake.

Excel column AA is not equal to 28
A1 -->28
A2 --> 29
etc
Jack

24-Mar-08 06:37 AM

am not sure what you mean exactly but one way might be:

k =3D ActiveCell.Column

23-Mar-08 04:49 PM

I need to convert column index (shown as letters) to the number.
For example:
Column: ABCD
What is the corresponding number of that column?
Jack

am not sure what you mean exactly but one way might be:

k = ActiveCell.Column

24-Mar-08 06:37 AM

Hello Jack,

Use the Cells method to create a Range address and return only the
column number.

For example:
C = Cells(1, "ABCD").Column

Sincerely,
Leith Ross

23-Mar-08 06:50 PM

Thank you.
That will work only, when my app is connected to Excel.
I need more general solution.
Jack

23-Mar-08 07:07 PM

Uh, no. You HAVE made a mistake, but you were closer the first time.

AA = 27
AB = 28

There's no such thing as column A1. A1 is a cell.

First, you have to consider whether you're going to support only version of
Excel before 2007, which only supported 256 columns (IX, or something like
that) or Excel 2007 as well, which supports...I think...1024 columns.

For the first case, where you can have at most two letters in the column
name, for anything beyond Z you have to take the first letter, convert it to
1 - 26, and multiply that by 26, then add the second letter.

For example, CQ = 3 * 26 + 17.

It's the same concept for the second case (Excel 2007), but now you have to
determine if you have 3 letters and multiply the first by 26 * 26 (26
squared) and then add the second * 26 and then the third.

In other words, you've got a base 26 number system.

23-Mar-08 07:07 PM

Your question does not make sense! What are you going to do wth this "number"
when you have got it? What *exactly* are you trying to do, and why?

Mike

23-Mar-08 11:32 PM

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

=GetColNum("AA") returns 27  ("IV") returns 256

I don't run 2007 so can't test past "IV"


Gord

24-Mar-08 02:17 AM

It does make sense, just again I have made a mistake.
In Excel 2007 the maximum column index is:  XFD
There are not 4 letters long column's indexes.

I know how to do it now..
Jack

24-Mar-08 04:49 AM

Not sure about "best", but the functions below should work well past any
thing you may want to handle. The ToNumber function (which is the one that
addresses your question) will convert letter combination up to BRUTMHYHIIZO
(which is converts to 9999999999999999). Likewise, the ToAlpha function
(which is the inverse of the ToNumber function) will accept values up to
9999999999999999 (which is converts to BRUTMHYHIIZO).

Rick

Function ToNumber(Value As String) As Variant
Dim x As Integer
If Format$(Value, "@@@@@@@@@@@@") > "BRUTMHYHIIZO" _
Or Value Like "*[!A-Za-z]*" Then
ToNumber = -1
Else
ToNumber = CDec(0)
For x = Len(Value) To 1 Step -1
ToNumber = ToNumber + _
(Asc(UCase$(Mid$(Value, x, 1))) - 64) * _
26 ^ (Len(Value) - x)
Next
End If
End Function

Function ToAlpha(ByVal Value As Variant) As String
Dim AsciiValue As Variant
If Len(Value) > 16 Or Value Like "*[!0-9]*" Then
ToAlpha = "###"
Else
Value = CDec(Value)
Do While Value > 0
AsciiValue = CDec(64 + Value - 26 * Int(Value / 26))
If AsciiValue = 64 Then AsciiValue = 90
ToAlpha = Chr$(AsciiValue) & ToAlpha
Value = Int(Value / 26)
If AsciiValue = 90 Then Value = Value - 1
Loop
End If
End Function

27-Mar-08 12:50 AM

Nice of you to tell the rest how you got there, but that's no mystery

I take it you finally, at last, worked out that there's 26 letters in
the alphabet, and you did some homework on powers and bases, and learnt
there's other counting systems than the decimal system

like binary, and octal, and hexadecimal, and ...

So 'A- Z' is 1 to 26

'AA' is 27, 'AB' is 28....  (like 26 + 1, 26 + 2)
The base is 26 ....
so each letter to the left is a power of 26

so 'ABC' is
26 * 26, (26^2 * 1 ('A')),
plus 26 *  2, (26^1 * 2 ('B')),
plus  1 *  3, (26^0 * 3 ('C'))

and 'XFD' then is 16384
or 2^14
or Hexadecimal 4000 - nice round number, innit?
strange that - 'XFD' being the last column .....

I think most of us in this newsgroup would know the above, but took you
a while to see it, didn't it

29-Mar-08 03:43 PM

Problem solved.
Just removed the reference, saved the project, restarted the project and
added again the same reference.
Any thoughts on that?
Jack

29-Mar-08 04:24 PM

Which reference did your remove and then add back in again? In one of your
previous responses, you said you needed to be able to convert the letters to
their numerical equivalent without being connected to Excel; that is, you
said you needed a general solution. What reference is associated with the

For your stated general solution, did you look at the ToNumber function I
posted in a previous response in this thread? While I admit this function
handles much, much more than you need to for your application, I would point
out that there is no time penalty in using it for the range of letters you
are interested it (the function only loops as many times as there are
letters in the argument passed into it).

Rick

29-Mar-08 04:47 PM

Sorry, it supposed to be a reply to the other thread.
Jack

29-Mar-08 05:17 PM

So, has the question you asked in **this** thread been resolved yet?

Rick