In this article, we will learn to **convert the alphabet to number in Excel**. In Excel, we can use different functions to change an alphabet to a number. Today, we will show **4 **easy ways. Using these methods, you can easily convert a single alphabet or multiple alphabets to numbers. So, without further delay, let’s start the discussion.

## How to Convert Alphabet to Number in Excel: 4 Ways

To explain the methods, we will use different datasets in different methods. Here, we will try to convert the alphabet to numbers. For example, **A=1**, **B=2**, and so on. You can see a quick overview below:

### 1. Convert Single Alphabet to Number in Excel

To convert a single alphabet to a number, we can mainly use three functions. They are **the COLUMN function**, **the CODE function**, and **the MATCH function**. Inside these functions, we may need to insert some necessary functions to create a formula. Let’s pay attention to the subsections to learn more about the formulas.

#### 1.1 Use COLUMN Function

The most common way to convert a single alphabet to a number is to use the **COLUMN **function. Inside it, we must use **the INDIRECT function**. You can see the alphabet in the **range B5:B9 **in the dataset below.

Let’s follow the steps below to see how we can convert them into numbers.

**STEPS:**

- First of all, select
**Cell B5**and type the formula below:

`=COLUMN(INDIRECT(B5&1))`

In Excel, the **INDIRECT **function returns the reference given by a text string and the **COLUMN **function gives us the column number of a reference. In this formula, we have used the **INDIRECT **function inside the formula which works as the reference for the **COLUMN **function. So, the **INDIRECT(B5&1) **becomes **A1 **first. Then, the formula becomes **COLUMN(A1)**. So, it returns **1**.

- Secondly, press
**Enter**and drag the**Fill Handle**down.

- Finally, you will see the number corresponding to each alphabet.

#### 1.2 Apply CODE Function

We can also use the **CODE **function for converting a single alphabet or letter to a number. The **CODE **function returns a numerical value for the first character in a text string. So, it will give us a correct numerical value if we use it in the case of a single alphabet. Let’s follow the steps below to get the result.

**STEPS:**

- Firstly, type the formula in
**Cell C5**:

`=CODE(UPPER(B5))-64`

Here, we have used **the UPPER function **inside the **CODE **function. The **UPPER **function converts the alphabet into an upper-case alphabet first. Then, the **CODE **function converts it into a numerical value. Here, the numerical value of **A **is **65**. That is why we are subtracting **64 **to get **1 **in the output.

- After that, press
**Enter**and drag the**Fill Handle**down to see the results.

#### 1.3 Insert MATCH Function

The **MATCH **function can provide another solution for converting an alphabet to a number in Excel. But we also need the help of the **ADDRESS **and **COLUMN **functions. Here, we will use the same dataset. Let’s follow the steps to learn more.

**STEPS:**

- In the first place, select
**Cell C5**and type the formula below:

`=MATCH(B5&"1",ADDRESS(1,COLUMN($1:$1),4),0)`

In this formula, the **ADDRESS **function returns a relative cell reference as text and then, the **MATCH **function gives us the desired output which is **1**.

- In the following step, press
**Enter**and drag the**Fill Handle**down.

**Read More: **How Excel Formulas Convert Text to Number

### 2. Change Multiple Alphabets to Numbers with TEXTJOIN & VLOOKUP Functions

In the previous method, we showed the way to change a single alphabet to a number. But in the second method, we will change multiple alphabets to numbers using the **TEXTJOIN **and **VLOOKUP **functions. To do so, we will use the dataset below. For example, we want the alphabet **ADE **to change to **145**. You can see the dataset contains a list of alphabets from **A **to **Z **with their relative positions. To create the formula, we will use a combination of **TEXTJOIN**, **VLOOKUP**, **IF**, **MID**, **ROW**, **INDIRECT**, and **LEN **functions.

Let’s observe the steps below to see how we can convert multiple alphabets to numbers in Excel.

**STEPS:**

- To begin with, select
**Cell C5**and type the formula below:

`=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),E5:F30,2,0))`

🔎 **How Does the Formula Work?**

We can break the formula into small parts to understand the whole mechanism.

**ROW(INDIRECT(“1:”&LEN(B5))):**This part of the formula returns the array of the row number. In this case, the array is**{1,2,3}**.**MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1):**The**MID**function gives us the characters in the specified position of the given string. So, the output for this part is**{A,D,E}**.**VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1))),E5:F30,2,0):**The**VLOOKUP**function looks for the corresponding numbers of the array**{A,D,E}**in the**range E5:F30**.**TEXTJOIN(“”,1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1))),E5:F30,2,0)):**Finally, the**TEXTJOIN**function joins the numbers and returns the output**145**. This function is available in**Excel 2019**and**Excel 365**.

- Press
**Enter**. - Finally, drag down the
**Fill Handle**to see the results.

**Read More:** How to Convert Text with Spaces to Number in Excel

### 3. Insert SUBSTITUTE Function to Transform Specific Alphabets to Number

If you have specific alphabets to transform into numbers, then you can use **the SUBSTITUTE function**. The **SUBSTITUTE **function replaces an existing text string with a new text. To explain the method, we will use a dataset that contains multiple text strings with the alphabets **A**, **B**, **C**, and **D**.

Let’s follow the steps below to learn the method of transforming specific alphabets into numbers.

**STEPS:**

- Firstly, type the formula below in
**Cell C5**:

`=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"A",1),"B",2),"C",3),"D",4)`

This formula substitutes **A **with **1**, **B **with **2**, **C **with **3**, and **D **with **4**. So, the output of **ABC **is **123**. If you want to add more alphabets, then you need to add another **SUBSTITUTE **function in the formula to replace that alphabet with a number.

- After typing the formula, hit
**Enter**. - Finally, drag the
**Fill Handle**down to get the results.

### 4. Apply VBA to Convert Letters to Numbers in Excel

In the last method, we will apply **VBA **to convert alphabets to numbers in Excel. **VBA **stands for **Visual Basic for Applications**. Here, we will create a function using **VBA **and then use it for changing alphabets to numbers. Interestingly, you can use it for both single and multiple alphabets. You can see the dataset below for this method.

Let’s pay attention to the steps below for more.

**STEPS:**

- First of all, go to the
**Developer**tab and select**Visual Basic**. It will open the**Visual Basic**window.

- In the
**Visual Basic**window, select**Insert >> Module**. It will open the**Module**window.

- Now, copy the code below and paste it into the
**Module**window:

```
Option Explicit
Function AlphabetToNumber(ByVal sSource As String) As String
Dim x As Integer
Dim sResult As String
For x = 1 To Len(sSource)
Select Case Asc(Mid(sSource, x, 1))
Case 65 To 90:
sResult = sResult & Asc(Mid(sSource, x, 1)) - 64
Case Else
sResult = sResult & Mid(sSource, x, 1)
End Select
Next
AlphabetToNumber = sResult
End Function
```

This **VBA **code creates a function that converts the alphabet to numbers. To apply the function for the lower and upper case alphabets, we will use the **UPPER **function inside the **AlphabetToNumber **function.

- After that, press
**Ctrl**+**S**to save the code. - In the following step, select
**Cell C5**and type the formula below:

`=AlphabetToNumber(UPPER(B5))`

- Finally, drag the
**Fill Handle**down to see the results for the rest of the cells.

## How to Convert Column Alphabet to Number in Excel

In Excel, the columns are numbered in the alphabet from **A **to **XFD**. Sometimes, we need to know the column number for different purposes. In that case, we can use the following methods to convert the column letters to numbers in Excel.

### 1. Convert Column Alphabet to Number Using Excel COLUMN Function

In the first method, we will use the **COLUMN **function to convert the alphabet to a number. We have also discussed this process in **Method 1** of the previous section. Let’s follow the steps below to see how we can convert the column alphabet to numbers.

**STEPS:**

- Firstly, select
**Cell C5**and type the formula below:

`=COLUMN(INDIRECT(B5&1))`

Here, the **INDIRECT(B5&1) **becomes **A1 **first. Then, the formula becomes **COLUMN(A1)**. So, it returns **1**.

- After that, press
**Enter**and drag the**Fill Handle**down.

- As a result, you will see the column numbers like the picture below.

**Read More: **Excel Convert to Number Entire Column

### 2. Apply User Defined Function to Change Column Letter to Number in Excel

**UDF **or **User Defined Function** can help users to change the column letter to a number in Excel. Here, we will create a function using simple codes in **VBA**. Later, we will use the function in a worksheet. Here, we will use the previous dataset.

Let’s follow the steps below to see how we can implement the **UDF **for converting column alphabets to numbers.

**STEPS:**

- First of all, go to the
**Developer**tab and select**Visual Basic**. It will open the**Visual Basic**window.

- Secondly, select
**Insert >> Module**in the**Visual Basic**window. It will open the**Module**window.

- Now, copy the code below and paste it into the
**Module**window:

```
Public Function ColNumber(cletter As String) As Long
ColNumber = Columns(cletter).Column
End Function
```

Here, **ColNumber **is the function that will return the column number and **cletter **is the argument of the function. Here, you need to enter the cell that contains the column letters.

- Press
**Ctrl**+**S**to save it. - In the following step, select
**Cell C5**and type the formula below:

`=ColNumber(B5)`

- After that, press
**Enter**and drag the**Fill Handle**down.

- Finally, you will be able to convert column letters to numbers in Excel.

**Download Practice Workbook**

## Conclusion

In this article, we have demonstrated **4 **easy ways to **Convert the Alphabet to Number in Excel**. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.