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.

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

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

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

**Download Practice Workbook**

**Related Articles**

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

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.

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 FunctionsUse of VBA User-Defined FunctionI hope the formulas and VBA code will fulfil your goal. I have attached the solution workbook; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemyThanks its working.

Dear

HarmanThanks for thanking me. You are most welcome. We are glad the solution worked perfectly.

Regards

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

Hello

Rahul DixitThanks for visiting our blog and sharing such an exciting problem! I have reviewed your problem and come up with an E

xcel VBA User-definedfunction. Please check the following:Excel VBA Code:Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcel & VBA DeveloperExcelDemy