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