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.

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

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

**the SUBSTITUTE function**replaces 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.

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

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

**Related Articles**

- How to Convert Green Triangle to Number in Excel
- 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

ExcelDemy