如何从Excel列信获得列号（或索引） [英] How to get Column Number(or index) from Excel Column Letter

问题描述

我搜寻过这个网站，并且搜寻了一个公式。我需要从以下字母计算Excel列号：

I have searched through this site and googled for a formula. I need to calculate an Excel column number FROM the letter such as:

A=1
B=2
..
AA=27
AZ=52
...
AAA=703

代码似乎是1在字母表的随机周期（AZ - > BA == off数字）后关闭数字。它也将看起来从两个不同的输入随机产生相同的整数：

The code seems to be 1 digit off after random cycles of the alphabet(AZ -> BA == off digit). It also will seemingly randomly produce the same integer from two different inputs:

GetColumnNumber(xlLetter : Text) : Integer //Start of function

 StringLength := STRLEN(xlLetter);
 FOR i := 1 TO StringLength DO BEGIN
 Letter := xlLetter[i];
   IF i>1 THEN
     Count += ((xlLetter[i-1]-64) * (i-1) * 26) - 1;
   Count += (Letter - 64);
 END;
 EXIT(Count); //return value

我的代码示例用C / AL编写，用于Dynamics NAV，我可以写C＃或vb.net，所以我不介意，如果一个例子是这些语言之一。

My code example is written in C/AL which is used for Dynamics NAV, but I can write C# or vb.net as well so I wouldn't mind if an example was in either of those languages.

推荐答案

在VBA中：

Public Function GetCol(c As String) As Long
    Dim i As Long, t As Long
    c = UCase(c)
    For i = Len(c) To 1 Step -1
        t = t + ((Asc(Mid(c, i, 1)) - 64) * (26 ^ (Len(c) - i)))
    Next i
    GetCol = t
End Function

这篇关于如何从Excel列信获得列号（或索引）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！