Convert column number to column letter
Use the following formula to convert a column number to a column letter:
The formula is entered in cell D3 shown in the image above, the column number is in cell B3.
Explaining formula in cell D3
You can follow along if you start the "Evaluate Formula" tool. You will find it on tab "Formula" on the ribbon.
Click "Evaluate" button to move to the next calculation step.
Step 1  Create a relative cell reference based on row and column number
The ADDRESS function returns a cell reference depending on what you use in the first (row) and second (column) argument.
The third argument lets you choose the type of cell reference the ADDRESS function returns. 4 is a relative cell reference.
ADDRESS(1, B3, 4)
becomes
ADDRESS(1, 1, 4)
and returns A1.
Step 2  Remove last characters based on column number
The column letters start with A and ends with XFD. A is column 1, AA is column 27, the first column reference that contains two letters.
AAA is the first column containing 3 letters and the corresponding column number is 703.
The MATCH function returns the position in the array of the largest value that is smaller than the lookup value (B3).
MATCH(B3, {1; 27; 703})
becomes
MATCH(1, {1; 27; 703})
and returns 1. The cell reference must have a single column letter.
Step 3  Extract a given number of characters from the start of the string
LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703}))
becomes
LEFT("A1", 1)
and returns A in cell D3.
Convert column letter to column number
The following formula converts a column letter to the corresponding column number.
Formula in cell C3:
If you don't want to use the INDIRECT function because it is volatile and may cause your worksheet to slow down considerably if used extensively, use this array formula.
If you rather use a regular formula, try this:
Explaining formula in cell C3
Step 1  Create a cell reference from a text string
The ampersand character & concatenates the value in cell B3 with 1.
The INDIRECT function converts the text string to a cell reference.
INDIRECT(B3&"1")
becomes
INDIRECT("A"&"1")
becomes
INDIRECT("A1")
and returns A1.
Step 2  Return column from cell reference
COLUMN(INDIRECT(B3&"1"))
becomes
COLUMN(A1)
and returns 1 in cell C3.
Convert column number to column letter (VBA)
User defined function in cell C3:
VBA code
Function ColumnLetter(col As Integer) As String ColumnLetter = Split(Cells(1, col).Address, "$")(1) End Function
Where to copy the code?
 Copy above custom function
 Go to VBA Editor (Alt+F11)
 Click "Insert" on the top menu
 Click "Module" to insert a module to your workbook
 Paste code into the code window
 Exit VBA Editor and return to Excel (Alt+Q)
Save your workbook
To be able to use the user defined function next time you open your workbook you need to save the workbook as a macroenabled workbook.
 Click "File" on the menu, or if you have an earlier version of Excel, click the office button.
 Click "Save As"

Click file extension dropdown list

Change the file extension to "Excel MacroEnabled Workbook (*.xlsm)".
Convert column letter to column number (VBA)
User defined function in cell C3:
VBA code
Function ColumnNumber(col As String) As Long ColumnNumber = Columns(col).Column End Function
Download Excel *.xlsm file
How to use absolute and relative references
What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD [โฆ]
How to quickly select a non contiguous range
A noncontiguous list is a list with occasional blank cells and that makes it harder to select the entire cell [โฆ]
How to find errors in a worksheet
Excel has great builtin features. The following one lets you search an entire worksheet for formulas that return an error. [โฆ]
Remove print preview lines (Page Breaks)
Have you ever wondered how these lines got there on a worksheet? They show where pages will break, in other [โฆ]
How to quickly select blank cells
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of [โฆ]
How to add a macro to your Excel Quick Access Toolbar
The Quick Access Toolbar is located at the very top of your Excel window, I highly recommend that you place your [โฆ]
The picture above shows data in column B, some cells contain nothing, they are blank. I will now go through [โฆ]
How to replace part of formula in all cells
This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than [โฆ]
Making your sheets easy to read is a fundamental approach of creating useful worksheets. Your message must be crystal clear, [โฆ]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form