ConvertToLetter 函数使用以下算法工作: 让iCol为列号。 如果iCol小于 1,则停止。 计算除以 26 的(iCol - 1)商和余数,并存储在变量a和b中。 将的b整数值转换为相应的字母字符 (0 => A,25 => Z) 并将其粘贴到结果字符串的前面。 设置为iCol除数a和循环。
https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter
So there’s no function that converts the excel column number to column letter directly. But we can combine SUBSTITUTE function with ADDRESS function to get the column letter using column index. Generic Formula =SUBSTITUTE(ADDRESS(1,column_number,4),1,””) ...
To convert a column number to letter we will use theADDRESSand theSUBSTITUTEFunctions. =SUBSTITUTE(ADDRESS(3,B3,4),"3","") The formula above returns the column letter in the position of the number in the referenced cell. Let’s walk through the above formula. ...
Let’s take an example to understand howwe can change the column number to a letterin Microsoft Excel. We have a serial no in column A. In column B, we need to put a formula to convert the numbers to letters. Follow the below given steps:- ...
我们看一下这个工具的应用界面，如下图：在B列中我们利用自定义函数NZNumToLetter（），输出的结果：二根据字母给出列的数值 我们看一下这个工具的应用界面，如下图：点击后代码会运行，程序中我给出的是”AG”列，要求求出这列的列数值，结果如下：Ø代码见程序文件：VBA_ConvertColumnNumberToLetter.xlsm ...
Function Convert_Col_Number_To_Letter(ColumnNumber As Double) As String Dim sLetter As String 'Split Address Letter & Row Number sLetter = Split(Cells(1, ColumnNumber).Address, "$")(1) 'Return only the Column Letter Convert_Col_Number_To_Letter = sLetter End Function ...
在B列中我们利用自定义函数NZNumToLetter(),输出的结果: 二 根据字母给出列的数值 我们看一下这个工具的应用界面,如下图: 点击后代码会运行,程序中我给出的是”AG”列,要求求出这列的列数值,结果如下: 代码见程序文件:VBA_ConvertColumnNumberToLetter.xlsm...
Function ConvertToLetter(column_num As Integer) ConvertToLetter = Split(Cells(1, column_num).Address, "$")(1) End Function 这个VBA宏代码可以将列号转换为列字母,并且可以直接在Excel中调用。 三、应用举例 1. 在VBA宏中使用 在VBA宏中,我们经常需要引用某一列的数据,这时就需要用到列号。通过上述介...
Using the Excel Object Model to convert column numbers to alphabetic formatOf course, you could always use the Excel object model to get the column letter. Much like the ADDRESS function, you use the Address property of the Range object to determine the address of the column and then simply...