How to Convert Alphabet to Number in Excel (4 Easy Ways)

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:

• 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:

• 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:

• 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:

• 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:

• 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

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

• 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:

• 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

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

• 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.

Related Articles

• How to Convert Green Triangle to Number in Excel
• How to Fix All Number Stored as Text in Excel
• How to Convert Bulk Text to Number in Excel
• How to Fix Convert to Number Error in Excel

<< Go Back to Convert Text to Number in Excel | Learn Excel