Convert Column Number to Letter in Excel ~ Different Ways!!
Tags: excelpreface, vbamacros, convertcolumnnumbert, excelbasics
Published: 2021-02-08 10:26:51

---
title: Convert Column Number to Letter in Excel ~ Different Ways!!
published: true
date: 2021-01-28 03:58:13 UTC
tags: ExcelPreface,VBAMacros,Convertcolumnnumbert,ExcelBasics
canonical_url: https://geekexcel.com/convert-column-number-to-letter-in-excel/
---

Before entering into this article, you should know what is the column in Excel? It is nothing but the vertical series of cells in an Excel sheet. Here, I will talk about **how to convert the column number to column letter in Excel** by using the **formula** and user-defined function in **VBA**. Let’s see them below!! Get an official version of ** MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)


## Convert column number to letter using Excel Formula:

- To convert the column number to letter, you can use the below formula.

**=[SUBSTITUTE](https://geekexcel.com/how-to-use-substitute-function-in-microsoft-excel-2013/)([ADDRESS](https://geekexcel.com/how-to-use-address-function-in-microsoft-excel-365/)(1,B1,4),”1″,” “)**

### Function Explanations:

- [**SUBSTITUTE Function**](https://geekexcel.com/how-to-use-substitute-function-in-microsoft-excel-2013/) – It helps to replace existing text with new text in a text string when you want to replace text based on its content, not position.
- [**ADDRESS Function**](https://geekexcel.com/how-to-use-address-function-in-microsoft-excel-365/) – This function will create a cell address from a given row and column.

### Practical Example:

Refer to the below example image.

- Here, we will give some column number in **[Column](https://geekexcel.com/excel-office-365-columns/) B**.

![Input](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Input.png)<figcaption id="caption-attachment-21493">Input</figcaption>

- Now, we need to find the Column letter of the corresponding column number.
- Apply the given formula to the [**formula bar**](https://geekexcel.com/how-to-use-formula-bar-for-various-purpose-in-excel-365/) section.
- Finally, it will return the result as shown below.

![Result 1](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Result-48.png)<figcaption id="caption-attachment-21492">Result 1</figcaption>

## Steps to convert column number to letter with VBA user-defined function:

Now we are going to see how to get the column letter from the given column number by the user-defined function in Excel VBA.

- First, you need to open the [**Visual Basic Editor**](https://geekexcel.com/shortcut-to-open-vba-editor-in-microsoft-excel-365/) by clicking the[**ALT**  +  **F11**](https://geekexcel.com/shortcut-to-open-vba-editor-in-microsoft-excel-365/) Keys.
- Then, you need to go to the **insert tab** and **select** the **module** option from the menu.

![select module option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/12/EXCEL_eadfxDWr3B.png)<figcaption id="caption-attachment-19857">Select module option</figcaption>

- After that, enter the below VBA codes in the code window, and save the code.

VBA code for Add date in footer using Macros in Excel

Public Function ConvertColumnNumberToLetter(Collet)  
ConvertColumnNumberToLetter= Split(Cells(1, Collet).Address, “$”)(1)  
End Function

![Save the code](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Save-the-code-1.png)<figcaption id="caption-attachment-21489">Save the code</figcaption>

- Then get back to the workbook and select the blank cell where you want to get the result.
- Apply the below formula in that cell and press the enter key.

**= ConvertColumnNumberToLetter(B1)**

![Enter the formula](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Enter-the-formula-27.png)<figcaption id="caption-attachment-21491">Enter the formula</figcaption>

- Finally, you can get the result as per your input data.

![Result 2](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Result-2-3.png)<figcaption id="caption-attachment-21490">Result 2</figcaption>

## Closure:

Here, we have explained the step-by-step procedure to **get the column letter based on a given column number in Excel** with different methods. Make use of this. Please share your worthwhile **feedback** in the below **comment** section. To learn more, check out our website **[Geek Excel](https://geekexcel.com/)!! **

### Read Ahead:

- **[Excel Formulas to Lookup the Entire Column ~ Simple Guide!!](https://geekexcel.com/excel-formulas-lookup-entire-column/)**
- **[How to Calculate the Table Columns in Excel ~ A Useful Guide!!](https://geekexcel.com/calculate-the-table-columns-excel/)**