blocks|key|1928068|text|此函数返回给定列号的列字母。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1928069|Function+Col_Letter(lngCol+As+Long)+As+String
++++Dim+vArr
++++vArr+=+Split(Cells(1,+lngCol).Address(True,+False),+"$")
++++Col_Letter+=+vArr(0)
End+Function|code-block|syntax|javascript|1928070|第100列的测试代码|1928071|Sub+Test()
++++MsgBox+Col_Letter(100)
End+Sub|1928072|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

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

testing code for column 100

<pre><code>Sub Test()
 MsgBox Col_Letter(100)
End Sub
</code></pre>

blocks|key|1928120|text|如果你不想使用range对象：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1928121|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+%2B+65)+&+s
++++++++n+=+(n+-+c)+\+26
++++Loop+While+n+>+0
++++ColumnLetter+=+s
End+Function|code-block|syntax|javascript|1928122|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you'd rather not use a range object:

<pre><code>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) &amp; s
 n = (n - c) \ 26
 Loop While n &gt; 0
 ColumnLetter = s
End Function
</code></pre>

blocks|key|1714667|text|对我有效的方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1714668|Cells(Row,Column).Address+|code-block|syntax|javascript|1714669|这将为您返回$AE$1格式引用。|1714670|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Something that works for me is:

<pre><code>Cells(Row,Column).Address 
</code></pre>

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

blocks|key|1928253|text|和一个使用递归的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1928254|Function+ColumnNumberToLetter(iCol+As+Long)+As+String

++++Dim+lAlpha+As+Long
++++Dim+lRemainder+As+Long

++++If+iCol+<=+26+Then
++++++++ColumnNumberToLetter+=+Chr(iCol+%2B+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+%2B+64)
++++End+If

End+Function|code-block|syntax|javascript|1928255|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

And a solution using recursion:

<pre><code>Function ColumnNumberToLetter(iCol As Long) As String

 Dim lAlpha As Long
 Dim lRemainder As Long

 If iCol &lt;= 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) &amp; Chr(lRemainder + 64)
 End If

End Function
</code></pre>

blocks|key|1928529|text|还有一种方法可以做到这一点。Brettdj's+answer让我想到了这一点，但是如果你使用这种方法，你不必使用变量数组，你可以直接转到一个字符串。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1928530|ColLtr+=+Cells(1,+ColNum).Address(True,+False)
ColLtr+=+Replace(ColLtr,+"$1",+"")|code-block|syntax|javascript|1928531|或者可以让它变得更紧凑一些|1928532|ColLtr+=+Replace(Cells(1,+ColNum).Address(True,+False),+"$1",+"")|1928533|请注意，这确实取决于引用cells对象中的第1行。|1928534|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/12797190/3357935^0|E|G|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|12|8|@]|9|@]|C|$G|H]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|C|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

Just one more way to do this. <a href="https://stackoverflow.com/a/12797190/3357935">Brettdj's answer</a> made 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.

<pre><code>ColLtr = Cells(1, ColNum).Address(True, False)
ColLtr = Replace(ColLtr, "$1", "")
</code></pre>

or can make it a little more compact with this

<pre><code>ColLtr = Replace(Cells(1, ColNum).Address(True, False), "$1", "")
</code></pre>

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

blocks|key|1929032|text|这可以通过使用公式来实现：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1929033|=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")|code-block|syntax|javascript|1929034|因此也可以根据请求将其编写为VBA函数：|1929035|Function+ColName(colNum+As+Integer)+As+String
++++ColName+=+Split(Worksheets(1).Cells(1,+colNum).Address,+"$")(1)
End+Function|1929036|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This is available through using a formula:

<pre><code>=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
</code></pre>

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

<pre><code>Function ColName(colNum As Integer) As String
 ColName = Split(Worksheets(1).Cells(1, colNum).Address, "$")(1)
End Function
</code></pre>

blocks|key|1929048|text|这是robartsd's+answer的一个版本(带有Jan+Wijninckx's+one+line+solution的味道)，使用递归而不是循环。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1929049|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)+%2B+Asc("A"))
End+Function|code-block|syntax|javascript|1929050|我已经使用以下输入进行了测试：|1929051|1+++=>+"A"
26++=>+"Z"
27++=>+"AA"
51++=>+"AY"
702+=>+"ZZ"
703+=>+"AAA"+
-1++=>+""
-234=>+""|1929052|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/15366979/3357935|1|https://stackoverflow.com/a/21820200/3357935^0|2|H|0|R|X|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]|$A|10|B|11|1|12]]|C|$]]|$1|D|3|E|5|F|7|13|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|14|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|15|8|@]|9|@]|C|$G|H]]|$1|M|3|-4|5|6|7|16|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]|U|$5|P|Q|R|C|$S|V]]]]

This is a version of <a href="https://stackoverflow.com/a/15366979/3357935">robartsd's answer</a> (with the flavor of <a href="https://stackoverflow.com/a/21820200/3357935">Jan Wijninckx's one line solution</a>), using recursion instead of a loop. 

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

I've tested this with the following inputs: 

<pre><code>1 =&gt; "A"
26 =&gt; "Z"
27 =&gt; "AA"
51 =&gt; "AY"
702 =&gt; "ZZ"
703 =&gt; "AAA" 
-1 =&gt; ""
-234=&gt; ""
</code></pre>

blocks|key|1928348|text|robertsd's+code很优雅，但要使其面向未来，请将n的声明更改为long类型|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1928349|如果你想要一个公式来避免宏的出现，下面的内容可以一直到702列|1928350|=IF(A1>26,CHAR(INT((A1-1)/26)%2B64),"")&CHAR(MOD(A1-1,26)%2B65)|code-block|syntax|javascript|1928351|其中A1是包含要转换为字母的列号的单元格。|1928352|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/15366979/3357935^0|0|F|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|C|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

<a href="https://stackoverflow.com/a/15366979/3357935">robertsd's code</a> is elegant, yet to make it future-proof, change the declaration of n to type long

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

<pre><code>=IF(A1&gt;26,CHAR(INT((A1-1)/26)+64),"")&amp;CHAR(MOD(A1-1,26)+65)
</code></pre>

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

blocks|key|1928383|text|这是一个基于上面@DamienFennelly's+answer的函数。如果你给我竖起大拇指，也给他竖起大拇指！|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1928384|Function+outColLetterFromNumber(iCol+as+Integer)+as+String
++++sAddr+=+Cells(1,+iCol).Address
++++aSplit+=+Split(sAddr,+"$")
++++outColLetterFromNumber+=+aSplit(1)
End+Function|code-block|syntax|javascript|1928385|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/20131777/3357935^0|8|O|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

This is a function based on <a href="https://stackoverflow.com/a/20131777/3357935">@DamienFennelly's answer</a> above. If you give me a thumbs up, give him a thumbs up too! :P

<pre><code>Function outColLetterFromNumber(iCol as Integer) as String
 sAddr = Cells(1, iCol).Address
 aSplit = Split(sAddr, "$")
 outColLetterFromNumber = aSplit(1)
End Function
</code></pre>

blocks|key|1715237|text|使用Excel+power有一种非常简单的方法:使用Range.Cells.Address属性，如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1715238|strCol+=+Cells(1,+lngRow).Address(xlRowRelative,+xlColRelative)|code-block|syntax|javascript|1715239|这将在第1行返回所需列的地址。|1715240|strCol+=+Left(strCol,+len(strCol)+-+1)|1715241|请注意，它是如此快速和强大，以至于您可以返回甚至存在的列地址！|1715242|使用Selection.Column属性用lngRow替换所需的列号！|1715243|entityMap^0|Q|J|0|0|0|0|0|2|G|L|6|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|10|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|11|8|@$9|12|A|13|B|C]|$9|14|A|15|B|C]]|D|@]|E|$]]|$1|S|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|T|$]]

There is a very simple way using Excel power: Use <code>Range.Cells.Address</code> property, this way:

<pre><code>strCol = Cells(1, lngRow).Address(xlRowRelative, xlColRelative)
</code></pre>

This will return the address of the desired column on row 1. Take it of the <code>1</code>:

<pre><code>strCol = Left(strCol, len(strCol) - 1)
</code></pre>

Note that it so fast and powerful that you can return column addresses that even exists!

Substitute <code>lngRow</code> for the desired column number using <code>Selection.Column</code> property!

blocks|key|1928854|text|这里有一个可以使用的简单的线性。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1928855|ColumnLetter+=+Mid(Cells(Row,+LastColA).Address,+2,+1)|code-block|syntax|javascript|1928856|它只适用于1个字母的列指定，但它对于简单的情况很好。如果您需要它只用于2个字母的指定，那么您可以使用以下命令：|1928857|ColumnLetter+=+Mid(Cells(Row,+LastColA).Address,+2,+2)|1928858|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here is a simple one liner that can be used.

<pre><code>ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 1)
</code></pre>

It will only work for a 1 letter column designation, but it is nice for simple cases. If you need it to work for exclusively 2 letter designations, then you could use the following:

<pre><code>ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 2)
</code></pre>

blocks|key|1928965|text|无论位于X行Y列的单元格的代码行中的哪一列，这都将起作用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1928966|Mid(Cells(X,Y).Address,+2,+instr(2,Cells(X,Y).Address,"$")-2)|code-block|syntax|javascript|1928967|如果您有一个具有唯一定义名称“Cellname”的单元格：|1928968|Mid(Cells(1,val(range("Cellname").Column)).Address,+2,+instr(2,Cells(1,val(range("Cellname").Column)).Address,"$")-2)|1928969|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This will work regardless of what column inside your one code line for cell thats located in row X, in column Y:

<pre><code>Mid(Cells(X,Y).Address, 2, instr(2,Cells(X,Y).Address,"$")-2)
</code></pre>

If you have a cell with unique defined name "Cellname":

<pre><code>Mid(Cells(1,val(range("Cellname").Column)).Address, 2, instr(2,Cells(1,val(range("Cellname").Column)).Address,"$")-2)
</code></pre>

blocks|key|1929861|text|所以我来晚了，但我想贡献另一个答案，其他人还没有解决这个问题，它不涉及数组。您可以使用简单的字符串操作来完成此操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1929862|Function+ColLetter(Col_Index+As+Long)+As+String

++++Dim+ColumnLetter+As+String

++++'Prevent+errors;+if+you+get+back+a+number+when+expecting+a+letter,+
++++'++++you+know+you+did+something+wrong.
++++If+Col_Index+<=+0+Or+Col_Index+>=+16384+Then
++++++++ColLetter+=+0
++++++++Exit+Function
++++End+If

++++ColumnLetter+=+ThisWorkbook.Sheets(1).Cells(1,+Col_Index).Address+++++'Address+in+$A$1+format
++++ColumnLetter+=+Mid(ColumnLetter,+2,+InStr(2,+ColumnLetter,+"$")+-+2)++'Extracts+just+the+letter

++++ColLetter+=+ColumnLetter
End+Sub|code-block|syntax|javascript|1929863|在获得$A$1格式的输入后，使用Mid函数，从位置2开始计算第一个$，然后使用InStr找到第二个$在字符串中出现的位置，然后减去2以计算该起始位置。|offset|length|style|CODE|1929864|这使您可以适应所有可能的列。因此，ColLetter(1)返回"A"，而ColLetter(16384)返回"XFD"，这是我的Excel版本的最后一列。|1929865|entityMap^0|0|0|3|4|G|3|X|1|13|5|1D|1|0|H|C|10|G|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]|$I|V|J|W|K|L]|$I|X|J|Y|K|L]|$I|Z|J|10|K|L]|$I|11|J|12|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@$I|14|J|15|K|L]|$I|16|J|17|K|L]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|P|$]]

So I'm late to the party here, but I want to contribute another answer that no one else has addressed yet that doesn't involve arrays. You can do it with simple string manipulation.

<pre><code>Function ColLetter(Col_Index As Long) As String

 Dim ColumnLetter As String

 'Prevent errors; if you get back a number when expecting a letter, 
 ' you know you did something wrong.
 If Col_Index &lt;= 0 Or Col_Index &gt;= 16384 Then
 ColLetter = 0
 Exit Function
 End If

 ColumnLetter = ThisWorkbook.Sheets(1).Cells(1, Col_Index).Address 'Address in $A$1 format
 ColumnLetter = Mid(ColumnLetter, 2, InStr(2, ColumnLetter, "$") - 2) 'Extracts just the letter

 ColLetter = ColumnLetter
End Sub
</code></pre>

After you have the input in the format <code>$A$1</code>, use the <code>Mid</code> function, start at position 2 to account for the first <code>$</code>, then you find where the second <code>$</code> appears in the string using <code>InStr</code>, and then subtract 2 off to account for that starting position.

This gives you the benefit of being adaptable for the whole range of possible columns. Therefore, <code>ColLetter(1)</code> gives back "A", and <code>ColLetter(16384)</code> gives back "XFD", which is the last possible column for my Excel version.

blocks|key|1715554|text|获取列名的简单方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1715555|Sub+column()

cell=cells(1,1)
column+=+Replace(cell.Address(False,+False),+cell.Row,+"")
msgbox+column

End+Sub|code-block|syntax|javascript|1715556|我希望这会有帮助=)|1715557|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Easy way to get the column name

<pre><code>Sub column()

cell=cells(1,1)
column = Replace(cell.Address(False, False), cell.Row, "")
msgbox column

End Sub
</code></pre>

I hope it helps =)

blocks|key|1929061|text|来自brettdj的解决方案非常好用，但如果你出于与我相同的原因遇到这个潜在的解决方案，我想我会提供我的替代解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1929062|我遇到的问题是根据MATCH()函数的输出滚动到特定的列。我没有将列号转换为其列字母parallel，而是临时将引用样式从A1切换到R1C1。这样，我就可以滚动到列号，而不必使用VBA函数。若要在两种引用样式之间轻松切换，可以使用以下VBA代码：|1929063|Sub+toggle_reference_style()

If+Application.ReferenceStyle+=+xlR1C1+Then
++Application.ReferenceStyle+=+xlA1
Else
++Application.ReferenceStyle+=+xlR1C1
End+If

End+Sub|code-block|syntax|javascript|1929064|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

The solution from brettdj works fantastically, but if you are coming across this as a potential solution for the same reason I was, I thought that I would offer my alternative solution.

The problem I was having was scrolling to a specific column based on the output of a MATCH() function. Instead of converting the column number to its column letter parallel, I chose to temporarily toggle the reference style from A1 to R1C1. This way I could just scroll to the column number without having to muck with a VBA function. To easily toggle between the two reference styles, you can use this VBA code:

<pre><code>Sub toggle_reference_style()

If Application.ReferenceStyle = xlR1C1 Then
 Application.ReferenceStyle = xlA1
Else
 Application.ReferenceStyle = xlR1C1
End If

End Sub
</code></pre>

blocks|key|1715830|text|在brettdj答案的基础上，这里将使列号的输入成为可选的。如果省略了列号输入，该函数将返回调用该函数的单元格的列字母。我知道仅使用ColumnLetter(COLUMN())也可以实现这一点，但我认为如果它能巧妙地理解这一点就更好了。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1715831|Public+Function+ColumnLetter(Optional+ColumnNumber+As+Long+=+0)+As+String
++++If+ColumnNumber+=+0+Then
++++++++ColumnLetter+=+Split(Application.Caller.Address(True,+False,+xlA1),+"$")(0)
++++Else
++++++++ColumnLetter+=+Split(Cells(1,+ColumnNumber).Address(True,+False,+xlA1),+"$")(0)
++++End+If
End+Function|code-block|syntax|javascript|1715832|这个函数的权衡是，由于IF测试，它会比brettdj的答案慢得多。但是，如果该函数被重复使用了很长时间，就会感觉到这一点。|1715833|entityMap^0|1U|M|0|0|B|2|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

Furthering on brettdj answer, here is to make the input of column number optional. If the column number input is omitted, the function returns the column letter of the cell that calls to the function. I know this can also be achieved using merely <code>ColumnLetter(COLUMN())</code>, but i thought it'd be nice if it can cleverly understand so.

<pre><code>Public Function ColumnLetter(Optional ColumnNumber As Long = 0) As String
 If ColumnNumber = 0 Then
 ColumnLetter = Split(Application.Caller.Address(True, False, xlA1), "$")(0)
 Else
 ColumnLetter = Split(Cells(1, ColumnNumber).Address(True, False, xlA1), "$")(0)
 End If
End Function
</code></pre>

The trade off of this function is that it would be very very slightly slower than brettdj's answer because of the <code>IF</code> test. But this could be felt if the function is repeatedly used for very large amount of times.

blocks|key|1716059|text|这里是一个较晚的答案，只是为了在1-3个字符列的情况下使用Int()和If的简单方法：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1716060|Function+outColLetterFromNumber(i+As+Integer)+As+String

++++If+i+<+27+Then+++++++'one-letter
++++++++col+=+Chr(64+%2B+i)
++++ElseIf+i+<+677+Then++'two-letter
++++++++col+=+Chr(64+%2B+Int(i+/+26))+&+Chr(64+%2B+i+-+(Int(i+/+26)+*+26))
++++Else+++++++++++++++++'three-letter
++++++++col+=+Chr(64+%2B+Int(i+/+676))+&+Chr(64+%2B+Int(i+-+Int(i+/+676)+*+676)+/+26))+&+Chr(64+%2B+i+-+(Int(i+-+Int(i+/+676)+*+676)+/+26)+*+26))
++++End+If

++++outColLetterFromNumber+=+col

End+Function|code-block|syntax|javascript|1716061|entityMap^0|T|5|Z|2|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

Here is a late answer, just for simplistic approach using <code>Int()</code> and <code>If</code> in case of 1-3 character columns:

<pre><code>Function outColLetterFromNumber(i As Integer) As String

 If i &lt; 27 Then 'one-letter
 col = Chr(64 + i)
 ElseIf i &lt; 677 Then 'two-letter
 col = Chr(64 + Int(i / 26)) &amp; Chr(64 + i - (Int(i / 26) * 26))
 Else 'three-letter
 col = Chr(64 + Int(i / 676)) &amp; Chr(64 + Int(i - Int(i / 676) * 676) / 26)) &amp; Chr(64 + i - (Int(i - Int(i / 676) * 676) / 26) * 26))
 End If

 outColLetterFromNumber = col

End Function
</code></pre>

blocks|key|1716164|text|Function+fColLetter(iCol+As+Integer)+As+String
++On+Error+GoTo+errLabel
++fColLetter+=+Split(Columns(lngCol).Address(,+False),+":")(1)
++Exit+Function
errLabel:
++fColLetter+=+"%25ERR%25"
End+Function|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1716165|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Function fColLetter(iCol As Integer) As String
 On Error GoTo errLabel
 fColLetter = Split(Columns(lngCol).Address(, False), ":")(1)
 Exit Function
errLabel:
 fColLetter = "%ERR%"
End Function
</code></pre>

blocks|key|1929727|text|下面是一个用Pascal+(Delphi)编写的简单函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1929728|function+GetColLetterFromNum(Sheet+:+Variant;+Col+:+Integer)+:+String;
begin
++Result+:=+Sheet.Columns[Col].Address;++//+from+Col=100+-->+'$CV:$CV'
++Result+:=+Copy(Result,+2,+Pos(':',+Result)+-+2);
end;|code-block|syntax|javascript|1929729|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here, a simple function in Pascal (Delphi).

<pre><code>function GetColLetterFromNum(Sheet : Variant; Col : Integer) : String;
begin
 Result := Sheet.Columns[Col].Address; // from Col=100 --&gt; '$CV:$CV'
 Result := Copy(Result, 2, Pos(':', Result) - 2);
end;
</code></pre>

blocks|key|1716283|text|此公式将根据范围(即A1)给出列，其中范围是单个单元格。如果给定了一个多单元格区域，它将返回左上角的单元格。注意，两个单元格引用必须相同：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1716284|MID(CELL("address"，A1),2，SEARCH("$"，CELL("address"，A1),2)-2)|1716285|它的工作原理：|1716286|CELL("+property+"，"+range+")根据使用的属性返回范围的特定值。在本例中是单元格地址。address属性返回值$col$row，即A1+->+$A$1。MID函数解析出$符号之间的列值。|1716287|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

This formula will give the column based on a range (i.e., A1), where range is a single cell. If a multi-cell range is given it will return the top-left cell. Note, both cell references must be the same:

MID(CELL("address",A1),2,SEARCH("$",CELL("address",A1),2)-2)

How it works:

CELL("property","range") returns a specific value of the range depending on the property used. In this case the cell address.
The address property returns a value $[col]$[row], i.e. A1 -> $A$1.
The MID function parses out the column value between the $ symbols.

blocks|key|1715719|text|Sub+GiveAddress()
++++Dim+Chara+As+String
++++Chara+=+""
++++Dim+Num+As+Integer
++++Dim+ColNum+As+Long
++++ColNum+=+InputBox("Input+the+column+number")

++++Do
++++++++If+ColNum+<+27+Then
++++++++++++Chara+=+Chr(ColNum+%2B+64)+&+Chara
++++++++++++Exit+Do
++++++++Else
++++++++++++Num+=+ColNum+/+26
++++++++++++If+(Num+*+26)+>+ColNum+Then+Num+=+Num+-+1
++++++++++++If+(Num+*+26)+=+ColNum+Then+Num+=+((ColNum+-+1)+/+26)+-+1
++++++++++++Chara+=+Chr((ColNum+-+(26+*+Num))+%2B+64)+&+Chara
++++++++++++ColNum+=+Num
++++++++End+If
++++Loop

++++MsgBox+"Address+is+'"+&+Chara+&+"'."
End+Sub|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1715720|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Sub GiveAddress()
 Dim Chara As String
 Chara = ""
 Dim Num As Integer
 Dim ColNum As Long
 ColNum = InputBox("Input the column number")

 Do
 If ColNum &lt; 27 Then
 Chara = Chr(ColNum + 64) &amp; Chara
 Exit Do
 Else
 Num = ColNum / 26
 If (Num * 26) &gt; ColNum Then Num = Num - 1
 If (Num * 26) = ColNum Then Num = ((ColNum - 1) / 26) - 1
 Chara = Chr((ColNum - (26 * Num)) + 64) &amp; Chara
 ColNum = Num
 End If
 Loop

 MsgBox "Address is '" &amp; Chara &amp; "'."
End Sub
</code></pre>

blocks|key|1929132|text|列号中的列字母可以通过以下步骤使用公式提取|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1929133|使用地址公式计算列地址，使用MID和FIND函数提取列字母|1929134|示例：|1929135|地址(1000,1000，1)|1929136|结果查找(F15，2，$ALL$1000=MID(“$”，F15,2)-2)|1929137|结果ALL+asuming+F15包含步骤1的结果|1929138|我们可以一气呵成地写|1929139|MID(ADDRESS(1000,1000,1),2，FIND("$"，ADDRESS(1000,1000,1),2)-2)|1929140|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|V|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|W|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|X|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|Y|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|Q|$]]

Column letter from column number can be extracted using formula by following steps 
1. Calculate the column address using ADDRESS formula 
2. Extract the column letter using MID and FIND function 
 
Example: 
1. ADDRESS(1000,1000,1) 
 results $ALL$1000 
2. =MID(F15,2,FIND("$",F15,2)-2) 
 results ALL asuming F15 contains result of step 1 
 
In one go we can write 
 MID(ADDRESS(1000,1000,1),2,FIND("$",ADDRESS(1000,1000,1),2)-2)

blocks|key|1929306|text|这只适用于REFEDIT+..。通常使用uphere代码简写版本...易于阅读和理解/使用$的poz|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1929307|Private+Sub+RefEdit1_Change()

++++Me.Label1.Caption+=+NOtoLETTER(RefEdit1.Value)+'+you+may+assign+to+a+variable++var=....'

End+Sub

Function+NOtoLETTER(REFedit)

++++Dim+First+As+Long,+Second+As+Long

++++First+=+InStr(REFedit,+"$")+++++++++++++++++'first+poz+of+$
++++Second+=+InStr(First+%2B+1,+REFedit,+"$")+++++'second+poz+of+$

++++NOtoLETTER+=+Mid(REFedit,+First+%2B+1,+Second+-+First+-+1)+++'extract+COLUMN+LETTER

End+Function|code-block|syntax|javascript|1929308|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

this is only for REFEDIT ... generaly use uphere code
shortly version... easy to be read and understood / 
it use poz of $ 

<pre><code>Private Sub RefEdit1_Change()

 Me.Label1.Caption = NOtoLETTER(RefEdit1.Value) ' you may assign to a variable var=....'

End Sub

Function NOtoLETTER(REFedit)

 Dim First As Long, Second As Long

 First = InStr(REFedit, "$") 'first poz of $
 Second = InStr(First + 1, REFedit, "$") 'second poz of $

 NOtoLETTER = Mid(REFedit, First + 1, Second - First - 1) 'extract COLUMN LETTER

End Function
</code></pre>

blocks|key|1929488|text|Cap+A是65，所以：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1929489|MsgBox+Chr(ActiveCell.Column+%2B+64)|offset|length|style|CODE|1929490|可在：http://www.vbaexpress.com/forum/showthread.php?6103-Solved-get-column-letter找到|1929491|entityMap|0|LINK|mutability|MUTABLE|url|http://www.vbaexpress.com/forum/showthread.php?6103-Solved-get-column-letter^0|0|0|Y|0|3|24|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|V|8|@]|9|@$D|W|E|X|1|Y]]|A|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]]]

Cap A is 65 so:

<code>MsgBox Chr(ActiveCell.Column + 64)</code>

Found in: <a href="http://www.vbaexpress.com/forum/showthread.php?6103-Solved-get-column-letter" rel="nofollow">http://www.vbaexpress.com/forum/showthread.php?6103-Solved-get-column-letter</a>

blocks|key|1716118|text|如果只是转换为ascii数字并使用Chr()将其转换回字母呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1716119|col_letter+=+Chr(Selection.Column+%2B+96)|1716120|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

what about just converting to the ascii number and using Chr() to convert back to a letter?

col_letter = Chr(Selection.Column + 96)

blocks|key|1928579|text|这里有另一种方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1928580|{

++++++Sub+find_test2()

++++++++++++alpha_col+=+"A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,W,Z"+
++++++++++++MsgBox+Split(alpha_col,+",")(ActiveCell.Column+-+1)+

++++++End+Sub

}|code-block|syntax|javascript|1928581|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here's another way:

<pre><code>{

 Sub find_test2()

 alpha_col = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,W,Z" 
 MsgBox Split(alpha_col, ",")(ActiveCell.Column - 1) 

 End Sub

}
</code></pre>

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

For example, entering 100 should return <code>CV</code>.

Function to convert column number to letter?

有没有人有Excel VBA函数可以从数字中返回列字母？例如，输入100应返回CV。

问将列数转换为字母的函数？
EN

回答 26

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将列数转换为字母的函数？EN

回答 26

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将列数转换为字母的函数？
EN