Assign Value to each word in a string

HockeyDiablo · Sep 6, 2016

I am looking to assign a value to words, then total the value. I am only coming up with solutions that add one value and not all of them.

Cat=1
Dog=2
Bird=3
Lizard=4

Example:

[TABLE="class: grid, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A "String"[/TD]
[TD="align: center"]B "Solution"[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Cat Dog Bird[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Dog[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Bird Lizard[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Cat Dog[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

Thank you

JoeMo · Sep 6, 2016

Here's a UDF you can try.
Sheet2

*	A	B
String	Sum

<colgroup><col style="font-weight:bold; *******3**** "><col style="*******82px;"><col style="*******64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Cat Dog Bird[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]Dog[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]Bird Lizard[/TD]
[TD="align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]Cat Dog[/TD]
[TD="align: center"]3[/TD]

</tbody>

Spreadsheet Formulas

Cell	Formula
B2	=sumthings(A2)

Excel tables to the web >> Excel Jeanie HTML 4

Code:

Function SumThings(S As String) As Double
Dim Creatures As Variant, Vals As Variant, V As Variant, i As Long, j As Long
Creatures = Array("Cat", "Dog", "Bird", "Lizard")
Vals = Array(1, 2, 3, 4)
V = Split(S, " ")
For i = 0 To UBound(V)
    For j = 0 To UBound(Creatures)
        If V(i) = Creatures(j) Then
            SumThings = SumThings + Vals(j)
        End If
    Next j
Next i
End Function

NOTE: This is case sensitive as posted

HockeyDiablo · Sep 6, 2016

JoeMo said:
Here's a UDF you can try.
Sheet2

* A B
String Sum

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Cat Dog Bird[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]Dog[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]Bird Lizard[/TD]
[TD="align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]Cat Dog[/TD]
[TD="align: center"]3[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula
B2 =sumthings(A2)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Code:

Function SumThings(S As String) As Double Dim Creatures As Variant, Vals As Variant, V As Variant, i As Long, j As Long Creatures = Array("Cat", "Dog", "Bird", "Lizard") Vals = Array(1, 2, 3, 4) V = Split(S, " ") For i = 0 To UBound(V) For j = 0 To UBound(Creatures) If V(i) = Creatures(j) Then SumThings = SumThings + Vals(j) End If Next j Next i End Function

NOTE: This is case sensitive as posted

Exactly what I need, thank you kindly

HockeyDiablo · Sep 6, 2016

JoeMo said:
Here's a UDF you can try.
Sheet2

* A B
String Sum

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Cat Dog Bird[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]Dog[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]Bird Lizard[/TD]
[TD="align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]Cat Dog[/TD]
[TD="align: center"]3[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula
B2 =sumthings(A2)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Code:

Function SumThings(S As String) As Double Dim Creatures As Variant, Vals As Variant, V As Variant, i As Long, j As Long Creatures = Array("Cat", "Dog", "Bird", "Lizard") Vals = Array(1, 2, 3, 4) V = Split(S, " ") For i = 0 To UBound(V) For j = 0 To UBound(Creatures) If V(i) = Creatures(j) Then SumThings = SumThings + Vals(j) End If Next j Next i End Function

NOTE: This is case sensitive as posted

Just curious on how you could make this not case sensitive? Easy?

Llupo01 · Sep 6, 2016

I see its too late and my solution is not so variable and sofisticated

its not a functioin....you can change your range depending in which cells you have your animals

but you dont need it anymore. Anyway....

Code:

Sub test()

Dim cat As Integer
Dim dog As Integer
Dim bird As Integer
Dim lizard As Integer
Dim cell As Range
Dim rng As Range
Dim word1 As String
Dim word2 As String
Dim word3 As String
Dim word4 As String

word1 = "cat"
word2 = "dog"
word3 = "bird"
word4 = "lizard"

Set rng = [b9:b12]


cat = 1
dog = 2
bird = 3
lizard = 4

For Each cell In rng.Cells
    
    If Not cell.Find(word1) Is Nothing Then
      cell.Offset(0, 1).Value = cell.Offset(0, 1).Value + cat
    End If
    
    If Not cell.Find(word2) Is Nothing Then
      cell.Offset(0, 1).Value = cell.Offset(0, 1).Value + dog
    End If
    
    
    If Not cell.Find(word3) Is Nothing Then
      cell.Offset(0, 1).Value = cell.Offset(0, 1).Value + bird
    End If
    
    If Not cell.Find(word4) Is Nothing Then
      cell.Offset(0, 1).Value = cell.Offset(0, 1).Value + lizard
    End If
 

Next



End Sub

JoeMo · Sep 6, 2016

HockeyDiablo said:
Just curious on how you could make this not case sensitive? Easy?

Easy. This revision is NOT case sensitive:

Code:

Function SumThings(S As String) As Double
Dim Creatures As Variant, Vals As Variant, V As Variant, i As Long, j As Long
Creatures = Array("Cat", "Dog", "Bird", "Lizard")
Vals = Array(1, 2, 3, 4)
V = Split(S, " ")
For i = 0 To UBound(V)
    For j = 0 To UBound(Creatures)
        If LCase(V(i)) = LCase(Creatures(j)) Then
            SumThings = SumThings + Vals(j)
        End If
    Next j
Next i
End Function

Eric W · Sep 6, 2016

You can do it with formulas too:

	A	B	C
String	Solution	Word	Value
Cat Dog Bird	Cat
Dog	Dog
Bird Lizard	Bird
Cat Dog	Lizard

<tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]

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

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

[TD="align: center"]3[/TD]

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

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

[TD="align: center"]4[/TD]

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

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

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

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

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

</tbody>

Sheet8

Array Formulas

Cell

<tbody>
[TH="align: left"]Formula[/TH]

[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=SUM( ((LEN(A2)-LEN(SUBSTITUTE(A2,$D$2:$D$5,"")))/LEN($D$2:$D$5)*$E$2:$E$5) )}

<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 [/TD]

</tbody>

Enter the formula in B2, change the ranges to match your sheet, then confirm with Control+Shift+Enter. Then drag it down the column.

To make it non case-sensitive, change the formula to:
=SUM(((LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($D$2:$D$5),"")))/LEN($D$2:$D$5)*$E$2:$E$5))
confirmed with Control+Shift+Enter.

There some issues with this. If you have a word in your cell that partially matches your word list, it will count it. Doggie will be counted as dog, and catastrophe will be counted as cat. You'll have to look at your actual word list to see if this will matter. You'll probably want to stick with Joe's UDF since you already have that working.

Rick Rothstein · Sep 6, 2016

Eric W said:
You can do it with formulas too:

Your formula is not robust though...

Catholic Dogma Birdie

Rick Rothstein · Sep 6, 2016

Rick Rothstein said:
Your formula is not robust though...

Catholic Dogma Birdie

However, if we assume only spaces surrounding the words (that is, no parentheses, commas, periods, etc.), then this modification to your formula appears to work more robustly...

{=SUM(((LEN(" "&A2&" ")-LEN(SUBSTITUTE(" "&A2&" "," "&$D$2:$D$5&" ","")))/LEN(" "&$D$2:$D$5&" ")*$E$2:$E$5))}

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

Eric W · Sep 7, 2016

I did mention some issues!

Eric W said:
There some issues with this.

However, nice job in improving the formula. I should have thought of that.

Assign Value to each word in a string

HockeyDiablo

Board Regular

Excel Facts

JoeMo

MrExcel MVP

HockeyDiablo

Board Regular

HockeyDiablo

Board Regular

Llupo01

Active Member

JoeMo

MrExcel MVP

Eric W

MrExcel MVP

Rick Rothstein

MrExcel MVP

Rick Rothstein

MrExcel MVP

Eric W

MrExcel MVP

Similar threads

Forum statistics

Share this page

Assign Value to each word in a string

Board Regular

Excel Facts

MrExcel MVP

Board Regular

Board Regular

Active Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock