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

This is the sample dataset.

### Method 1 – Converting Single Alphabet letters to Numbers in Excel

#### 1.1 Using the COLUMN 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

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

STEPS:

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

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.

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

The sample dataset contains a list of alphabet letters.

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

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.

Read More: Excel Convert to Number Entire Column

### 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``````

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.

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

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.

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.

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

2. Thanks its working.

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.

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.