# Assign values to letters

#### offsup

##### New Member
Dear Mr. Excel,

I'm new to this forum.

I have been trying to assign a number value to each of 3 letter characters but have not found the correct function. Can you help?

Ultimately, one of these 3 letters will be typed into each cell within 1 column and the 3 corresponding numbers will be used in a formula to calculate the product of a second cell multiplied by this number to appear in a 3rd cell.

Any help will be appreciated. I have tried using 'VALUE' but I keep getting an error. Thank you.

offsup

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### lenze

##### Legend
Hi and welcome to the board!!

Can you be a little more specific? What are the 3 letter characters and what numbers correspond to each? In stead of a formula or function, would you consider a VBA(code) approach? I think I know what you want, but need to check.

lenze

#### offsup

##### New Member
Hi Lenze,

Thank you for your quick response.

In column D, I would like to assign each cell 1 of 3 letters (C, F, P).

When I type 1 of those letters, I would like to create a formula that will substitute each letter for a number (C=5, F=6, P=7).

Later on, I will calculate Column B multiplied by Column D to appear in Column E. Column E=(Column B * Column D)

Let me know. Your help is terrific.

offsup

#### lenze

##### Legend
So, you want the entry in column D to automatically change to the corresponding number, or do you want the number displayed elsewhere?

lenze

#### offsup

##### New Member
Dear Lenze,

For the sake of future crisises', let me ask you for both instances. The first instance, where Column D should automatically change to the assigned number.

The second instance, where the assigned number Appears in Column D.5 (since I was shortsighted and used Column E in my example.)

Thank you.

offsup

#### lenze

##### Legend
This code will will change the letter entered in "D" to the corresponding number
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 4 Then Exit Sub
Application.EnableEvents = False
Select Case UCase(Target)
Case "C": Target = 5
Case "F": Target = 6
Case "P": Target = 7
Case Else
MsgBox "Invalid Entry"
Application.Undo
End Select
Application.EnableEvents = True
End Sub``````

This will place the numberic value in column F. Adjust as needed
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 4 Then Exit Sub
Application.EnableEvents = False
Select Case UCase(Target)
Case "C": Cells(Target.Row, 6) = 5 'Place Value in Column F (column 6). Change the 6 to use another column
Case "F": Cells(Target.Row, 6) = 6
Case "P": Cells(Target.Row, 6) = 7
Case Else
MsgBox "Invalid Entry"
Application.Undo
End Select
Application.EnableEvents = True
End Sub``````

You can only use 1 of these a a time!!! Place the code in the WorkSheet module. Rightclick the sheet tab and choose "View Code". Paste the code in the white panel.

HTH
lenze

Replies
2
Views
148
Replies
3
Views
213
Replies
1
Views
123
Replies
1
Views
157
Replies
0
Views
346

1,190,737
Messages
5,982,661
Members
439,790
Latest member
jonaust

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back