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

budrowilson

New Member
Joined
Jan 6, 2017
Messages
4
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?
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

budrowilson

New Member
Joined
Jan 6, 2017
Messages
4
I forgot to mention: I'm perfectly comfortable implementing a VBA function if that would be easier.
 

Fennek

Active Member
Joined
Nov 21, 2016
Messages
279
Hi,

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

regards
 

budrowilson

New Member
Joined
Jan 6, 2017
Messages
4
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.

LetterPoints
a1
b3
c3
d2
e1
f4
g2
h4
i1
j8
k5
l1
m3
n1
o1
p3
q10
r1
s1
t1
u1
v4
w4
x8
y4
z10
0

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,744

ADVERTISEMENT

Maybe something like this:

Excel 2012
ABCDEF
1a1
2b3
3c3
4d2ash6
5e1main6
6f4state5
7g2willow12
8h4live oak14
9i1weird &()% name15
10j8
11k5
12l1
13m3
14n1
15o1
16p3
17q10
18r1
19s1
20t1
21u1
22v4
23w4
24x8
25y4
26z10

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Array Formulas
CellFormula
F4{=SUM(SUMIF($A$1:$A$26,MID(E4,ROW(INDIRECT("1:"&LEN(E4))),1),$B$1:$B$26))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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

Fennek

Active Member
Joined
Nov 21, 2016
Messages
279
Hi,

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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top