Excel columns: Converting numbers to letters and vice versa

J

Jack

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
 
J

Jack

As usual I have made a mistake.

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

Jack

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
 
L

Leith Ross

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

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
 
J

Jack

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

Jeff Johnson

As usual I have made a mistake.

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

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

Mike Williams

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

Your question doesn't 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
 
G

Gord Dibben

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
 
J

Jack

It does make sense, just again I've 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
 
R

Rick Rothstein \(MVP - VB\)

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?

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
 
A

argusy

Jack said:
It does make sense, just again I've 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
<snip>

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
 
J

Jack

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

Rick Rothstein \(MVP - VB\)

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
"general solution" you sought?

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
 
R

Rick Rothstein \(MVP - VB\)

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

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top