## Convert column number to column letter

*Article last updated on July 13, 2018*

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 macro-enabled 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 drop-down list

- Change the file extension to "Excel Macro-Enabled 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

Locate lookup values in a table [HYPERLINK]

Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When […]

Quickly select a data set or an excel defined table [HYPERLINK]

If you often copy data sets or tables, the following technique might be interesting! The animated gif shows two hyperlinks, […]

How to quickly find the maximum or minimum value [Formula]

Question: I have three columns and how do I identify the largest and smallest number? Where is the value? I […]

How to quickly select a non contiguous range

A non-contiguous 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 built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]

Select and delete cells and formulas that return nothing

Deleting empty cells in a cell range is easy. Select the cell range Press function key F5 Click "Special..." button […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

DATEVALUE function not working

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must […]

Sum values containing text based on a condition

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

List files in a folder and subfolders [UDF]

This blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]

Search all workbooks in a folder

Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]

This post describes how to copy values between sheets. I am using the invoice template sheet. This macro copies rows […]

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form