Assigning a value to a letter

[andreathomas5]

Hi there,

I really need some help in assigning a letter (A,B,C,D,etc) a value and then whenever I type that letter in a cell, it will give me the value of it.

Example...

A=10
B=20
C=30

If I type C in a cell, I would like for it to convert into a 30, is that even possible? Or is there a similar way of doing this?

Thanks in advance!

 

[trunten]

Not without VBA I'm afraid. However, if you were up to modifying your requirements slightly you could have a lookup column next to your values column which did a simple vlookup on a table which you have set up that contains all of your letter values.
Eg
=VLOOKUP($A2, letterValues, 2, FALSE)

 

[andreathomas5]

Okay i have no idea what a lookup is, but what you are suggesting is to put a column to the right of my letters A B C D with the value I want for each of them and then use the formula you posted??

I'm sorry if I ask "dumb" questions, but I only know basic excel!

 

[Michael M]

Here's an example, that replaces the Upper Case letter with the number in Column "A" only
I'm sure there is a better way...I've seen this one before somewhere !

Sub worksheet_Change(ByVal target As Range)
If target.Column > 1 Then Exit Sub
If target.Value = "A" Then target.Value = 10
If target.Value = "B" Then target.Value = 20
If target.Value = "C" Then target.Value = 30
End Sub

 

[Misca]

Try pasteing the following to the sheet module (=double click the sheet name in your VBA editor) of the worksheet you want to use it on:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer
Dim Nmbr As Long

With Target
For i = 1 To Len(.Value)    'Adds the letter values up
    Select Case Mid(.Value, i, 1)
        Case Is = "A"
            Nmbr = Nmbr + 10
        Case Is = "B"
            Nmbr = Nmbr + 20
        Case Is = "C"
            Nmbr = Nmbr + 30
        Case Is = "D"
            Nmbr = Nmbr + 40
        Case Is = "E"
            Nmbr = Nmbr + 50
        Case Is = "F"
            Nmbr = Nmbr + 60
        Case Is = "G"
            Nmbr = Nmbr + 70
        Case Else
            Nmbr = Nmbr + Asc(Mid(.Value, i, 1))    'Adds the Asc-value of the digit
    End Select
Next i

    .Value = Nmbr

End With

End Sub

This macro sums the values of each letter in the cell. If you don't want the macro to sum the values, delete the lines "For i=1 to" and "Next i" and replace the i's with 1 in "Mid(.Value, i, 1)".

 

[GTO]

I'm not very good at formulas, but seems to work...

Excel Workbook
	A	B
1	F	60
Sheet6

 

[GTO]

Oopsie... That last zero should just be a pair of double quotes.

 

[andreathomas5]

Great, I've used the formula that GTO provided above and it worked like a charm

I used that one because that one seemed to be the easiest. Thanks to all the other members that gave me an answer

 

[GTO]

Thank you for the feedback Andrea

Mark

 

[Trebor76]

Here's another possibility:

=COLUMN(INDIRECT(A1&"1"))*10

HTH

Robert