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

Get FREE Advanced Excel Exercises with Solutions!

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:

excel convert alphabet to number


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.

Convert Single Alphabet to Number in Excel

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

Convert Single Alphabet to Number in Excel

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)

Convert Single Alphabet to Number in Excel

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.

Change Multiple Alphabets to Numbers with TEXTJOIN & VLOOKUP 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.

Insert SUBSTITUTE Function to Transform Specific Alphabets to Number

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.

Apply VBA to Convert Letters to Numbers in Excel

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

Apply VBA to Convert Letters to Numbers in Excel

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

Convert Column Alphabet to Number Using Excel COLUMN Function

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.

Apply User Defined Function to Change Column Letter to Number in Excel

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

Apply User Defined Function to Change Column Letter to Number in Excel

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo