Excel enables us to convert letters to numbers and numbers to letters. In this tutorial, we will learn how to convert letters to numbers and vice versa using the **SUBSTITUTE function, COLUMN function, and VBA.**

*Figure 1 – Example of how to convert column letter to a number*

**How to ****Convert Column Letter to number**

- We will prepare our table as shown in the figure below

* Figure 2 – Letter to number*

- We will highlight
**Cell C4**and insert the formula below in the formula bar

**=COLUMN(INDIRECT(B4&"1"))**

- We will press the
**Enter Key**

* Figure 3 – Using COLUMN Function to convert letters to a number*

- We will click on
**Cell C4**and drag down the fill handle tool to copy the formula down the column.

* Figure 4 – Column letter to a number*

**Using ****VBA to Convert Column Letter to a Column Number**

- Again, we will prepare a table as shown in the figure below

* Figure 5 – Setting up data to convert letters to numbers*

- We will press
**ALT + F11**to open the Microsoft Visual Basic Applications window

* Figure 6 – Using VBA to convert column letter*

- Next, we will go to
**Insert**and Select**Module** - In the blank space, we will enter the formula
**below:**

`Public Function ToColNum(ColN)`

` ToColNum = Range(ColN & 1).Column`

`End Function`

* Figure 7 – Convert column letter to number using VBA*

- We will
**save the code**and go back to the worksheet - We will enter the formula below in
**Cell C4**

`=Tocolnum("A")`

**Where A is the content of Cell B4**

* Figure 8 – Return with column letter in excel*

- We will press
**Enter**

* Figure 9 – Excel column letter to a number*

- Again, to get the column value for the content of
**Cell C5,**we will enter the formula

**=Tocolnum("BA")**

**Where BA is the content of Cell B5**

* Figure 10 – Letter to the column number *

- We will press the
**Enter key**

* Figure 11 – Excel column letter to a number*

- We will have this result

* Figure 12 – Excel returns with a column number*

**How to Convert ****Column Number to Column Letter**

- We will prepare our table as shown in the figure below

* Figure 13 – Setting up data for converting column number to letter*

- We will click on
**Cell C4**and insert the formula below in the formula bar

**=SUBSTITUTE(ADDRESS(1,B4,4),"1","")**

- We will press the
**Enter Key**

* Figure 14 – Convert column number to letter*

- We will click again on
**Cell C4**. Next, we will use the fill handle tool to drag down the formula into other cells in**Column C**

* Figure 15 – Excel returns with column letter*

**How to ****Convert Column Number to Column Letter Using VBA**

- We will create a table as shown below

* Figure 16 – Setting up data for converting number to letter in excel*

- We will press
**ALT + F11**to open the**Microsoft Visual Basic Applications window** - Next, we will go to the
**Insert**tab and Select**Module** - In the blank space, we will enter the formula below

`Public Function ToColletter(Collet)`

` ToColletter = Split(Cells(1, Collet).Address, "$")(1)`

`End Function`

* Figure 17 – Excel Column number to letter using VBA*

- Next, we will go back to the worksheet and enter the formula below in Cell
**C4**

**=Tocolnum("1")**

Where **1** is the content of Cell **B4**

* Figure 18 – Convert column number to letter using VBA*

- We will press the
**enter key**

* Figure 19 – Excel convert column number to letter*

- Again, to convert
**Cell B5**, we will enter the formula below into Cell C5

**=ToColletter(23)**

**Where 23 is the content of Cell B5**

- We will press the
**enter key**

* Figure 20 – Convert letter to a number*

- In the end, we will have this result

* Figure 21 – VBA for column number to letter*

**Instant Connection to an Excel Expert**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment