Microsoft Excel has patterns such as A, B, C, AA, BB, CC, AD, AA, AAB, and AAZ. Column 1 is named as A, B as 2, and 27 as AA. Therefore, finding a column letter is easy and possible, given its corresponding column number is easy. To convert a column number to the letter in Excel, you can follow the following steps. It is important to note that this function only converts integers into their corresponding alphanumeric text character. The column or row headings on the physical worksheet are not changed in appearance.
Steps to convert a column Number
It is important to note that we deal with 26 characters from A-Z. Remember, we don't have zero among these characters since A represents 1 while Z represents 26. There are two ways to tackle this problem;
- Say you have the number 676, it is still possible to get its base representation.
- Divide and get a remainder of 26
-
To get rid of zero, we get (25 26)to base 26; thus, its symbolic representation is YZ
It is also important to know how to convert the column headings into letters in Excel
1. Go to File Tab
2. Select Options
3. The Excel Options window appears. Click on the Formulas option
4. Ensure the R1CA option reference style is unchecked
5. Press on OK
6. The column numbers will turn into letters
How to turn column letters into numbers
Sometimes you might need to convert column letters to numbers. You can achieve it in the following easy steps.
1. Click on the File tab
2. Navigate down and click on options
3. Click on Formulas on the popup window that popups.
4. Navigate to the working with formulas section and check the box on the R1C1 reference style
5. Click ok
6. The column labels will turn to numbers
How to convert column numbers to text using VBA
1. Press Alt +F11
2. Copy and paste the code below
Sub ColNumToLetter()
Dim ColNumber As Long
Dim ColLetter As String
'Input Column Number
ColNumber = 200
'Convert To Column Letter
ColLetter = Split(Cells(1, ColNumber).Address, "$")(1)
'Display Result
MsgBox "Column " & ColNumber & " = Column " & ColLetter
End Sub
Save and run the macro