How to convert a column number into an excel column letter

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;

  1. Say you have the number 676, it is still possible to get its base representation.
  2. Divide and get a remainder of 26
  3. 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

%d bloggers like this: