Turning letters and words to number values in excel?

Question

I have a block of letters and words in an exel spreadsheet, and I want to give each letter a number value, and where there are words sum the result. How could I do this?

Answer 1

You do not specify the structure of 'a block of letters and words' or the values you wish to assign to each letter of the alphabet.



So, this solution is my interpretation of the exercise.



The following macro will create a table of letters A-Z in column IU and numbers 1-26 in column IV correspondingly.



It will then evaluate each text string in column A and display a message box stating whether the text string is a valid word found in the spell checker or not. If it is a valid word, it will return the value of the letters comprising each word from the table previously constructed. It will also place the value of the word in the appropriate cell in column B.



Copy the macro to the clipboard:



Sub ValidWords()

Dim i, LastRow, spCk

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

If Cells(i, "A").Value <> "" Then

spCk = Application.CheckSpelling(word:=Cells(i, "A"), _

IgnoreUppercase:=True)

If spCk = True Then

For j = 1 To Len(Cells(i, "A"))

x = Val(Application.VLookup(Mid(Cells(i, "A"), j, 1), _

Range("IU1:IV26"), 2, False))

wordval = wordval + x

Next j

MsgBox UCase(Cells(i, "A").Value) & " in " & _

Cells(i, "A").Address(0, 0) & _

" is a valid word." & " The value is " & wordval, _

vbOKOnly, "Result"

Cells(i, "B").Value = wordVal

ElseIf spCk = False Then

MsgBox UCase(Cells(i, "A").Value) & " in " & _

Cells(i, "A").Address(0, 0) & " is not a valid word.", _

vbOKOnly, "Invalid"

End If

wordval = 0

End If

Next i

End Sub



Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE



Paste the macro into the editing area to the right.



Close the VBE and return to the worksheet.



Press ALT + F8



When the Macros window opens, highlight the macro and click 'Options..'



Enter a letter to be used as a keyboard shortcut and click 'OK'.



To run the macro, press Ctrl + your shortcut letter.



If you add more text strings to column A, the macro will include them the next time it is called.



If your 'blocks' are in multiple columns, advise the cell range(s) and I will modify the macro.

Answer 2

You need individual countif functions, so at the bottom of your list I would suggest entering into separate cells the O, A, G and C and alongside the function =countif(A3:A50,"O"). This is assuming your data is in cells a2-a50. If you go up to the formula bar when you have done this function and just press the F4 function key, this will make your reference absolute so you can replicate it down to cover the A, G and C and then change the letter in the last part of the function for each one.

Answer 3

On sheet2 make a list of letters a to z and in the next column assign the numeric value you require ie a=1, b=2 etc

On sheet1 cell A1 enter TOTAL, B1 enter WORD, C1 enter LETTER1, D1 enter LETTER2 etc. Select C1&D1 and copy across row

In A2 enter =SUM($C2:BA2)

In B2 enter the word you want summed

In C2 enter =IF(ISERROR(INDEX(Sheet2!$B:$B, MATCH(MID($B2,COLUMN()-2,1),

Sheet2!$A:$A,0))),"", INDEX(Sheet2!$B:$B,MATCH(MID($B2, COLUMN()-2,1),Sheet2!$A:$A,0)))

Copy across to BA2 Hide cols C to BA if required.