## Excel formula: Convert letter to number

Sometimes, you want to convert a to 1, b to 2, c to 3 and so on in Excel sheet. However, to convert them one by one is a waste of time. In this tutorial, I introduce some Excel formulas to solve this problem.

** Convert single letter to a number in each cell**

To convert single letter to a number in each Excel cell, you can use below formula.

**Generic formula:**

COLUMN(INDIRECT(cell_reference&1)) |

**Arguments**

Cell_reference: the cell that contains the letter you want to convert to number. |

**How this formula work**

Take an example to explain the formula. Here you will convert the letter in cell C3 to number. Please use this formula.

=COLUMN(INDIRECT(C3&1)) |

Press** Enter** key.

**Explanation**

**INDIRECT** functiin: this function used to convert a text string to a valid reference. Here INDIRECT(C3&1) you can see it as these:

* INDIRECT(C3&1) **=INDIRECT(s&1) **=INDIRECT(s1)*

**COLUMN** function: the COLUMN function returns the number of column of the given reference. Now the formula is *COLUMN(s1)* which returns 19.

**Remarks:**

1. This formula returns the number of column, which means that if there are more than one letter in cell, it will return as below example shown.

2. The formula is case insensitive.

** Convert several letters to a string of numbers in each cell**

If you want to convert letters in each cell to numbers as below screenshot shown, you need a complex array formula.

**Generic formula:**

TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(cell_refer,ROW(INDIRECT("1:"&LEN(cell_refer))),1))),Rtable,2,0)) |

**Arguments**

Cell_refer: the cell that you want to convert the letters to numbers. |

Rtable:a table with two columns, one lists all letters, one lists the relative numbers. |

**How this formula work**

Supposing you want to convert the letters in range B3:B8 to numbers, please do as these:

Firstly, create a table to list the all letters and their relative numbers.

Then select the table range and go to the **Name box** (beside the Formula bar) to give it a ranged name, here the range name is Rtable. see screenshot:

Now use this formula:

=TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)) |

Press **Enter** key, all first letters of each word in cell A1 is extracted.

**Explanation**

**ROW(INDIRECT("1:"&LEN(B3)))**: the ROW function returns the number of row, there is the array formula, then it returns *{1;2;3}*. **MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)**: the MID function returns the character in the specific position of the given string. This formula can be seen as below *=MID(B3,{1;2;3},1) **=MID(“acd”,{1;2;3},1) **= {"a";"c";"d"}*

**VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)**: the VLOOKUP function is used to find the matched numbers of "a", "c","d" in the second column of range Rtable. Then it returns *{1;3;4}*.

**TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0))**: the TEXTJOIN function is a new function in Excel 2019 and 365, it is used to concatenated all texts with a delimiter. Here it returns *134*.

**Remarks:**

This formula is case insensitive.

**Note:**

Supposing a=1,b=2,c=3,d=4, you can use the formula as below:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"a",1),"b",2),"c",3),"d",4) |

Press **Enter** key.

This formula is case sensitive.

** Sample File**

#### Relative Formulas

- Remove First N Characters From Cell

Here introduces the formula to remove characters from left side of a text string in Excel. - Remove From Right Of Text

Here introduces the formula to remove characters from right side of a text string in Excel. - Extract The Last Word From Text String In Excel

This tutorial provides a formula to extract last word from the give text string. - Extract The First Word From Text String In Excel

This tutorial provides a formula to extract first word from the give text string.

#### Relative Functions

- INDIRECT function

Convert a text string to a valid reference. - COLUMN function

Return the number of column which formula appears or the column number of given reference. - ROW function

Return row number of a reference. - MID

Return the specific characters from the middle of text string .

**The Best Office Productivity Tools**

#### Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

- Designed for 1500 work scenarios, helps you solve 80% Excel problems.
- Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
- Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
- 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.

#### Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.

###### You are guest

###### or post as a guest, but your post won't be published automatically.

Be the first to comment.