Looping through Characters in a Cell, assigning value to them and summing

[budrowilson]

Hi there!

A friend of mine is participating in a scavenger hunt where she can score points based on road signs using Scrabble scoring. I was hoping to create a spreadsheet where she can figure out what are the most valuable roads in a city where she travels to so that she can optimally spend her time there. Finding the names of the roads in a place is relatively easy, as is creating the Scrabble letter value lookup table.

I've done quite a bit of research into analyzing the individual characters in a cell, but most of the resources are adding the numerical values located in a single cell. What I'm needing to do is loop through the characters, using a VLOOKUP to find the values of those characters, then output the sum of those values.

My road names start in E:4 and my Scrabble lookup table is 'Scrabble Legend'!$A$1:$B$27.

A sample road name might be "ash", which would total 6 points (a[1] + s[1] + 4[h]).

Thoughts?

 

[budrowilson]

I forgot to mention: I'm perfectly comfortable implementing a VBA function if that would be easier.

 

[Fennek]

Hi,

could you provide a list with the values for all characters?

regards

 

[budrowilson]

I input a blank space at the bottom with a value of "0" to handle spaces in the street names. The reason my street names appear so far over in the street names sheet is because I'm stripping out irrelevant data and formatting the streets appropriately for calculation.

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Letter[/TD]
[TD="class: xl65, width: 64"]Points[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]i[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]j[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]k[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]l[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]m[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]n[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]o[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]p[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]q[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]r[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]s[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]t[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]u[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]v[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]w[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]z[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Maybe something like this:

Excel 2012

	A	B	C	D	E	F
ash
main
state
willow
live oak
weird &()% name

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]a[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]b[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]c[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]d[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]e[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]f[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]g[/TD]
[TD="bgcolor: #FFFFFF, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF"]h[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF"]i[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF"]j[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF"]k[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF"]l[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF"]m[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF"]n[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFFFF"]o[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFFFF"]p[/TD]
[TD="bgcolor: #FFFFFF, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFFFF"]q[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FFFFFF"]r[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FFFFFF"]s[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FFFFFF"]t[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FFFFFF"]u[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #FFFFFF"]v[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #FFFFFF"]w[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #FFFFFF"]x[/TD]
[TD="bgcolor: #FFFFFF, align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #FFFFFF"]y[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #FFFFFF"]z[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]{=SUM(SUMIF($A$1:$A$26,MID(E4,ROW(INDIRECT("1:"&LEN(E4))),1),$B$1:$B$26))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



If there are characters in the name not in the table, they will be ignored. Except "*", don't use that in any street names.

 

[budrowilson]

That got it. Thank you!

 

[Fennek]

Hi,

too slow, but nevertheless:
For one cell (A1)

sub Fen()
Tx = lcase(cells(1,1))
Res = 0
for i = 1 to len(Tx)
    if mid(Tx, i, 1) like " " then v = 0
    if mid(Tx, i, 1) like "[aeilnorst]" then v = 1
    if mid(Tx, i, 1) like "[dg]" then v = 2
    if mid(Tx, i, 1) like "[bcmp]" then v = 3
    if mid(Tx, i, 1) like "[fhvy]" then v = 4
    if mid(Tx, i, 1) like "[k]" then v = 5
    if mid(Tx, i, 1) like "[jx]" then v = 8
    if mid(Tx, i, 1) like "[qz]" then v = 10
    Res = Res = v
next i
end sub

It si easy to re-write it to a function or built a loop.

regards

 

[Eric W]

Glad we could help!