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

#### budrowilson

##### New Member
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?

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### budrowilson

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

#### Fennek

##### Active Member
Hi,

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

regards

#### budrowilson

##### New Member
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.

 Letter Points a 1 b 3 c 3 d 2 e 1 f 4 g 2 h 4 i 1 j 8 k 5 l 1 m 3 n 1 o 1 p 3 q 10 r 1 s 1 t 1 u 1 v 4 w 4 x 8 y 4 z 10 0

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

#### Eric W

##### MrExcel MVP

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

</tbody>
Sheet3

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

</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.

#### budrowilson

##### New Member
That got it. Thank you!

#### Fennek

##### Active Member
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

#### Eric W

##### MrExcel MVP

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,659
Messages
5,838,642
Members
430,558
Latest member
Krampus

### 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