How to Convert Alphabet to Number in Excel – 4 Easy Methods

This is the sample dataset.

excel convert alphabet to number


Method 1 – Converting Single Alphabet letters to Numbers in Excel

1.1 Using the COLUMN Function

Convert Single Alphabet to Number in Excel

STEPS:

  • Select B5 and enter the formula below:
=COLUMN(INDIRECT(B5&1))

The INDIRECT function returns the reference given by a text string and the COLUMN function returns the column number of a reference. The INDIRECT(B5&1) becomes A1. Then, the formula becomes COLUMN(A1). It returns 1.

  • Press Enter and drag down the Fill Handle.

This is the output.


1.2 Applying the CODE Function

STEPS:

  • Enter the formula in C5:
=CODE(UPPER(B5))-64

Convert Single Alphabet to Number in Excel

the UPPER function inside the CODE function converts the alphabet into an upper-case. The CODE function converts it into a numerical value. Here, the numerical value of A is 65. 64 is subtracted to get 1.

  • Press Enter and drag down the Fill Handle  to see the result.


1.3 Inserting the MATCH Function

STEPS:

  • Select C5 and enter the formula below:
=MATCH(B5&"1",ADDRESS(1,COLUMN($1:$1),4),0)

Convert Single Alphabet to Number in Excel

The ADDRESS function returns a relative cell reference as text and the MATCH function returns the output: 1.

  • Press Enter and drag the Fill Handle down.

Read More: How Excel Formulas Convert Text to Number


Method 2 – Changing Multiple Alphabet letters to Numbers with the TEXTJOIN & VLOOKUP Functions

The sample dataset contains a list of alphabet letters.

Change Multiple Alphabets to Numbers with TEXTJOIN & VLOOKUP Functions

STEPS:

  • Select C5 and enter the formula below:
=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1))),E5:F30,2,0))

Formula Breakdown

  • ROW(INDIRECT(“1:”&LEN(B5))):  returns the array of the row number. Here, {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. The output 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 in the array {A,D,E} in E5:F30.
  • TEXTJOIN(“”,1,VLOOKUP(T(IF(1,MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1))),E5:F30,2,0)): The TEXTJOIN function joins the numbers and returns the output 145.
  • Press Enter.
  • Drag down the Fill Handle to see the result.

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


Method 3 – Inserting the SUBSTITUTE Function to Transform Specific Alphabet letters to Numbers

 

Insert SUBSTITUTE Function to Transform Specific Alphabets to Number

STEPS:

  • Enter the formula below in C5:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"A",1),"B",2),"C",3),"D",4)

The formula substitutes A with 1, B with 2, C with 3, and D with 4. The output of ABC is 123.

  • Press Enter.
  • Drag the Fill Handle down to see the result.


Method 4 – Applying a VBA to Convert Letters to Numbers in Excel

This is the sample dataset.

Apply VBA to Convert Letters to Numbers in Excel

STEPS:

  • Go to the Developer tab and select Visual Basic.The Visual Basic window opens.

  • In the Visual Basic window, select Insert >> Module. The Module window opens.

  • Enter the code below in the Module:
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 lower and upper cases, use the UPPER function inside the AlphabetToNumber function.

  • Press Ctrl + S to save the code.
  • Select C5 and enter the formula below:
=AlphabetToNumber(UPPER(B5))

  • Drag the Fill Handle down to see the result.


How to Convert Alphabet to Number in a Column in Excel.

1. Converting Alphabet to Number Using Excel COLUMN Function

STEPS:

  • Select Cell C5 and enter the formula below:
=COLUMN(INDIRECT(B5&1))

Convert Column Alphabet to Number Using Excel COLUMN Function

The INDIRECT(B5&1) becomes A1. The formula becomes COLUMN(A1) and returns 1.

  • Press Enter and drag the Fill Handle down.

You will see the column numbers.

Read More: Excel Convert to Number Entire Column


2. Applying the User Defined Function to Change Column Letter to Number in Excel

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

STEPS:

  • Go to the Developer tab and select Visual Basic to open the Visual Basic window.

  • Select Insert >> Module. The Module window opens.

  • Enter the code below in 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

ColNumber returns the column number and cletter is the argument of the function (enter the cell that contains the column letters).

  • Press Ctrl + S to save.
  • Select C5 and enter the formula below:
=ColNumber(B5)

  • Press Enter and drag the Fill Handle down.

This is the output.


Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

8 Comments
  1. I want to convert e.g
    AA=0101
    GF=0706
    WA=2301
    Still in search of this formula in excel… I’m very close to it like
    AA=11
    GF=76
    WA=231
    But we want 0 as leading above example when we convert A to I in number.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 25, 2024 at 5:18 PM

      Hello Harman

      Thanks for visiting our blog and sharing your query. You want 0 as leading when returning a number against a character. Don’t worry! I have come up with two solutions:

      Use of TEXTJOIN, TEXT & VLOOKUP Functions

      1. Select an empty cell.
      2. Insert the following formula: =TEXTJOIN("", 1, TEXT(VLOOKUP((IF(1, MID(B5, ROW(INDIRECT("1:" & LEN(B5))), 1))), $E$5:$F$30, 2, 0), "00"))
      3. Drag the Fill Handle icon to copy the formula down.

      Use of VBA User-Defined Function

      1. Go to Developer, followed by Visual Basic.
      2. Click on Insert followed by Module.
      3. Insert the following code in the module and save it:
        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 & Format(Asc(Mid(sSource, x, 1)) - 64, "00")
                    Case Else
                        sResult = sResult & Mid(sSource, x, 1)
                End Select
            Next
            
            AlphabetToNumber = sResult
        
        End Function
      4. Return to the sheet and select an empty cell.
      5. Insert the following formula: =AlphabetToNumber(UPPER(B5))
      6. Hit Enter to see an output like the following GIF:

      I hope the formulas and VBA code will fulfil your goal. I have attached the solution workbook; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  2. Thanks its working.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto May 6, 2024 at 12:32 PM

      Dear Harman
      Thanks for thanking me. You are most welcome. We are glad the solution worked perfectly.

      Regards
      ExcelDemy

  3. I want to convert
    1 = A
    2 = B
    3 = C
    4 = D
    5 = E
    6 = F
    7 = G
    8 = H
    9 = I
    0 = O

    and also wants to join them e.g. if I enter 45, 23, 67 then output should be DE, BC, FG respectively.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto May 30, 2024 at 9:48 AM

      Hello Rahul Dixit

      Thanks for visiting our blog and sharing such an exciting problem! I have reviewed your problem and come up with an Excel VBA User-defined function. Please check the following:

      Excel VBA Code:

      Function ConvertNumbersToLetters(inputStr As String) As String
      
          Dim numArray() As String
          Dim resultArray() As String
          Dim i As Integer
          Dim currentNum As String
          Dim convertedNum As String
          
          numArray = Split(inputStr, ",")
          ReDim resultArray(LBound(numArray) To UBound(numArray))
          
          For i = LBound(numArray) To UBound(numArray)
              currentNum = Trim(numArray(i))
              convertedNum = ""
              Dim j As Integer
              Dim currentChar As String
              
              For j = 1 To Len(currentNum)
                  currentChar = Mid(currentNum, j, 1)
                  Select Case currentChar
                      Case "1"
                          convertedNum = convertedNum & "A"
                      Case "2"
                          convertedNum = convertedNum & "B"
                      Case "3"
                          convertedNum = convertedNum & "C"
                      Case "4"
                          convertedNum = convertedNum & "D"
                      Case "5"
                          convertedNum = convertedNum & "E"
                      Case "6"
                          convertedNum = convertedNum & "F"
                      Case "7"
                          convertedNum = convertedNum & "G"
                      Case "8"
                          convertedNum = convertedNum & "H"
                      Case "9"
                          convertedNum = convertedNum & "I"
                      Case "0"
                          convertedNum = convertedNum & "O"
                      Case Else
                          convertedNum = convertedNum & currentChar
                  End Select
              Next j
              
              resultArray(i) = convertedNum
          Next i
          
          ConvertNumbersToLetters = Join(resultArray, ", ")
      
      End Function

      Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  4. Reply
    SUMMERTIME SAGA APK Dec 8, 2024 at 12:46 PM

    Great post! I never knew there were so many methods to convert letters to numbers in Excel. The step-by-step instructions were super helpful. Can’t wait to try them out in my next project!

    • Hello,

      You are most welcome. Thanks for your feedback and appreciation. Glad to hear that our step-by-step guide is super helpful to you.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo