vba 将列号转换为字母的函数？

Function to convert column number to letter?

提问by mezamorphic

Does anyone have an Excel VBA function which can return the column letter(s) from a number?

有没有人有可以从数字返回列字母的 Excel VBA 函数？

For example, entering 100should return CV.

例如，输入100应返回CV。

回答by brettdj

This function returns the column letter for a given column number.

此函数返回给定列号的列字母。

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

testing code for column 100

第 100 列的测试代码

Sub Test()
    MsgBox Col_Letter(100)
End Sub

回答by robartsd

If you'd rather not use a range object:

如果您不想使用范围对象：

Function ColumnLetter(ColumnNumber As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String

    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function

回答by Damian Fennelly

Something that works for me is:

对我有用的是：

Cells(Row,Column).Address 

This will return the $AE$1 format reference for you.

这将为您返回 $AE$1 格式参考。

回答by ashleedawg

• For example:MsgBox Columns( 9347 ).Addressreturns.

• 例如：MsgBox Columns( 9347 ).Address返回。

---

---

To return ONLYthe column letter(s): Split((Columns(Column Index).Address(,0)),":")(0)

仅返回列字母：Split((Columns(Column Index).Address(,0)),":")(0)

• For example:MsgBox Split((Columns( 2734 ).Address(,0)),":")(0)returns.

• 例如：MsgBox Split((Columns( 2734 ).Address(,0)),":")(0)返回。

---

---

??

??

---

---

回答by OSUZorba

Just one more way to do this. Brettdj's answermade me think of this, but if you use this method you don't have to use a variant array, you can go directly to a string.

还有一种方法可以做到这一点。Brettdj的回答让我想到了这一点，但是如果你使用这种方法你就不必使用变体数组，你可以直接去一个字符串。

ColLtr = Cells(1, ColNum).Address(True, False)
ColLtr = Replace(ColLtr, "", "")

or can make it a little more compact with this

或者可以让它更紧凑一点

ColLtr = Replace(Cells(1, ColNum).Address(True, False), "", "")

Notice this does depend on you referencing row 1 in the cells object.

请注意，这取决于您引用单元格对象中的第 1 行。

回答by Nikolay Ivanov

And a solution using recursion:

以及使用递归的解决方案：

Function ColumnNumberToLetter(iCol As Long) As String

    Dim lAlpha As Long
    Dim lRemainder As Long

    If iCol <= 26 Then
        ColumnNumberToLetter = Chr(iCol + 64)
    Else
        lRemainder = iCol Mod 26
        lAlpha = Int(iCol / 26)
        If lRemainder = 0 Then
            lRemainder = 26
            lAlpha = lAlpha - 1
        End If
        ColumnNumberToLetter = ColumnNumberToLetter(lAlpha) & Chr(lRemainder + 64)
    End If

End Function

回答by Alistair Collins

This is available through using a formula:

这可以通过使用公式获得：

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

and so also can be written as a VBA function as requested:

因此也可以根据要求编写为 VBA 函数：

Function ColName(colNum As Integer) As String
    ColName = Split(Worksheets(1).Cells(1, colNum).Address, "$")(1)
End Function

回答by alexanderbird

This is a version of robartsd's answer(with the flavor of Jan Wijninckx's one line solution), using recursion instead of a loop.

这是robartsd 答案的一个版本（具有Jan Wijninckx 的单行解决方案的风格），使用递归而不是循环。

Public Function ColumnLetter(Column As Integer) As String
    If Column < 1 Then Exit Function
    ColumnLetter = ColumnLetter(Int((Column - 1) / 26)) & Chr(((Column - 1) Mod 26) + Asc("A"))
End Function

I've tested this with the following inputs:

我已经使用以下输入对此进行了测试：

1   => "A"
26  => "Z"
27  => "AA"
51  => "AY"
702 => "ZZ"
703 => "AAA" 
-1  => ""
-234=> ""

回答by Jan Wijninckx

robertsd's codeis elegant, yet to make it future-proof, change the declaration of n to type long

robertsd 的代码很优雅，但要使其面向未来，请将 n 的声明更改为 long 类型

In case you want a formula to avoid macro's, here is something that works up to column 702 inclusive

如果您想要一个避免宏的公式，这里有一些适用于第 702 列的内容

=IF(A1>26,CHAR(INT((A1-1)/26)+64),"")&CHAR(MOD(A1-1,26)+65)

where A1 is the cell containing the column number to be converted to letters.

其中 A1 是包含要转换为字母的列号的单元格。

回答by mtbink.com

LATEST UPDATE: Please ignore the function below, @SurasinTancharoen managed to alert me that it is broken at n = 53.
For those who are interested, here are other broken values just below n = 200:

最新更新：请忽略下面的功能，@SurasinTancharoen 设法提醒我它在n = 53.
对于那些感兴趣的人，以下是其他损坏的值n = 200：

Please use @brettdj function for all your needs. It even works for Microsoft Excel latest maximum number of columns limit: 16384should gives XFD

请使用@brettdj 函数满足您的所有需求。它甚至适用于 Microsoft Excel 最新的最大列数限制：16384应该给出XFD

END OF UPDATE

更新结束

---

---

The function below is provided by Microsoft:

以下功能由微软提供：

Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

Source: How to convert Excel column numbers into alphabetical characters

来源：如何将 Excel 列号转换为字母字符

APPLIES TO

适用于

• Microsoft Office Excel 2007
• Microsoft Excel 2002 Standard Edition
• Microsoft Excel 2000 Standard Edition
• Microsoft Excel 97 Standard Edition

• 微软办公软件Excel 2007
• Microsoft Excel 2002 标准版
• Microsoft Excel 2000 标准版
• Microsoft Excel 97 标准版