How to Convert Alphabet to Number in Excel

This is the sample dataset.

Method 1 – Converting Single Alphabet letters to Numbers in Excel

the COLUMN function, the CODE function, and the MATCH function can be used.

1.1 Using the COLUMN Function

Use the COLUMN function with the INDIRECT function.

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

The CODE function returns a numerical value for the first character in a text string.

STEPS:

Enter the formula in C5:

=CODE(UPPER(B5))-64

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

The MATCH function will be used with the ADDRESS and COLUMN functions.

STEPS:

Select C5 and enter the formula below:

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

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

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

The sample dataset contains a list of alphabet letters. You can use a formula, combining the TEXTJOIN, VLOOKUP, IF, MID, ROW, INDIRECT, and LEN 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.

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

the SUBSTITUTE functionreplaces an existing text string with a new text.

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.

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

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

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.

2. Applying the 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

Here, 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

Convert Alphabet to Number.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!

4 Comments

harman Apr 25, 2024 at 3:48 PM

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 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
  Select an empty cell.
  
  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"))
  
  Drag the Fill Handle icon to copy the formula down.
  Use of VBA User-Defined Function
  Go to Developer, followed by Visual Basic.
  
  Click on Insert followed by Module.
  
  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
  
  Return to the sheet and select an empty cell.
  
  Insert the following formula: =AlphabetToNumber(UPPER(B5))
  
  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
harman May 6, 2024 at 12:08 PM

Thanks its working.
- Reply
  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

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

Method 1 – Converting Single Alphabet letters to Numbers in Excel

1.1 Using the COLUMN Function

1.2 Applying the CODE Function

1.3 Inserting the MATCH Function

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

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

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

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

1. Converting Alphabet to Number Using Excel COLUMN Function

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

Related Articles

Leave a reply Cancel reply