How to Convert a Column Number to Letter in Excel Using VBA

1. Convert a Column Number to Letter in Excel Using VBA
2. the Split() Function
3. Conclusion

This article describes different functions that convert a column number to a column letter in Microsoft Excel using VBA.

Convert a Column Number to Letter in Excel Using VBA

There are several functions in Microsoft Excel that allow you to convert a column number to a column letter. This is super useful for the users who are aware of the column number and are mapping it to a column letter.

These solutions can lower the hassle of searching the exact column from just a number provided.

Solution 1 - Use the Cells().Address Function

If you know the row and the column in Microsoft Excel and want to find the corresponding reference, use the following function in VBA.

Syntax:

Cells(Row,Column).Address

The image below is a depiction of the function.

Solution 2 - Use the Columns().Address Function

If you are provided with a column number only, you can still find the corresponding column letter for it in the simplest way. Use the Columns() function with the Address() function to get the corresponding column reference.

Syntax:

Columns(Column Index).Address

The Columns() function was executed with 100 as the Column Index. The result of the function is achieved in the following format $ColumnLetter:$ColumnLetter.

The working of the function can be seen in the image below.

Solution 3 - Use the Split() Function With the Columns().Address Function

The Split() function can be used on the answer obtained from solution 2 to only get the column letters instead of the entire address. Here is the syntax of how the Split function can be used over the Columns().Address function.

Split((Columns(Column Index).Address(,0)),":")(0)

When using the Split() function, the ColumnAbsolute parameter in the Address() function is passed as false, and the substrings are split on the colon Delimiter. The first substring contains the column letter; hence, a zero is attached at the end of the Split() function to specify that the first substring is required.

Note the Split() and functions are described in a later section of the article. Scroll below for more details.

When the statement is executed with a Column Index value of 100, it returns the following results.

Solution 3 - Use the Cells() Function With the Address() Function

Another technique to get the column letter from a column number is using the Cells() function with the Address() function. Here is how to get the column letter.

Syntax:

Cells(1, Column Number).Address

The statement returns the cell reference in the following format: $CV$1. Execution of the code statement provides the following results.

Refinement of Solution 3

Solution 3 can be further refined to extract the column letter only. The Split() function can be used for this purpose.

Here is the statement that returns the column letter only.

Split(Cells(1, Column Number).Address(True, False), "$")(0)

The Split() function breaks the address string on the "$" sign, and zero at the end of the statement indicates that only the first substring is required.

The code statement produces the following results.

Solution 4 - Create a Function

This solution does not make use of a range object. It is estimated to have a faster execution speed, and it presumably makes use of less memory.

In addition, it does not require access to the Excel API. Here is a function that calculates the column letter from the column number provided as a parameter.

Function test(ColNum As Long) As String

    Dim n As Long
    Dim ch As Byte
    Dim str As String

    n = ColNum

    Do
        ch = ((n - 1) Mod 26)
        str = Chr(ch + 65) & str
        n = (n - ch) \ 26

    Loop While n > 0
    test = str

End Function

The test() function can be called in the main() subroutine to get the column letter corresponding to the column number. The following is the depiction of the execution of the program.

the Split() Function

A Split() function breaks the input expression into multiple substrings based on a delimiter passed to the function.

Syntax:

Split ( Expression, [Delimiter], [Limit], [Compare] )

The Split() function takes an Expression as a parameter and breaks it into several substrings. This compulsory parameter must be passed to the String() function.

The second parameter is the Delimiter; an optional argument passed to the Split() function. A space character is a default considered the Delimiter, and the user can specify a different Delimiter to divide the string, such as commas, colons, etc.

The Limit parameter is an optional argument that specifies the total number of substrings the user requires. By default, the value for Limit is -1; therefore, it returns all substrings.

The Compare parameter is an optional argument that takes 0 or 1 as input. It specifies the type of comparison that you need the function to perform.

• An exact, case-sensitive comparison is made when 0 is passed as an argument. Therefore, if the Delimiter is "XYZ", the function will ONLY accept "XYZ" and it will reject "xyz".
• An case-insensitive comparison is made when 1 is passed as an argument. Therefore, if the Delimiter is "XYZ", the function will also accept "xyz".

Conclusion

Microsoft Office applications offer numerous features for their users. With the addition of programming in the VBA language, it has been easier for users to do automated tasks with only a few lines of code.

VBA supports numerous functions that allow the user to perform different tasks. Different approaches are available to achieve the same task, as seen in the article. Numerous approaches are used to get the column letter corresponding to a column number provided as input.