Home / Formulas / Convert a Letter to a Number

Convert a Letter to a Number

The Excel HelpDesk Support team received the following request for help:

Problem – Convert a Letter to a Number for a Column

I am looking for a formula I can place in a column that will convert a letter to a number value. For instance, s=1, m=1.5, L=2.5. I want it to be a continous rule for the column, leaving find and replace not feasible.

Create a vLookup to translate a letter to a number

For this case the standard drag and drop will not work as we need to drag a column of letter values and have that display a relevant number. The result we are looking for would be something like.

letter to number start

 

Now to automatically populate the Number column with values that relate to the Letter column we can use the vLookup function. We start by creating a list of Letters and Numbers as the reference and then use vLookup to translate those values in the worksheet with the Letter is used.

vLookup Sample

 

Now we have a formula that can be used to automatically populate the Number column in another worksheet based on a unique list of Letters and Numbers in the Reference worksheet. Click on the download link below to access the sample file used in this post

Download Sample Workbook

 

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

About Excel Help Desk

Check Also

vLookup and Match Names and Numbers

For this problem we received some sample workbooks. We then used some formulas to compare the information and match the number to the name