Assign Value to each word in a string

[HockeyDiablo]

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:

	A "String"	B "Solution"
1	Cat Dog Bird	6
2	Dog	2
3	Bird Lizard	7
4	Cat Dog	3

<tbody>
</tbody>

​

Thank you

 

[JoeMo]

Here's a UDF you can try.
Sheet2

*	A	B
1	String	Sum
2	Cat Dog Bird	6
3	Dog	2
4	Bird Lizard	7
5	Cat Dog	3

<colgroup><col style="font-weight:bold; *******3**** "><col style="*******82px;"><col style="*******64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =sumthings(A2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

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]

Here's a UDF you can try.
Sheet2

*	A	B
1	String	Sum
2	Cat Dog Bird	6
3	Dog	2
4	Bird Lizard	7
5	Cat Dog	3

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =sumthings(A2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

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]

Here's a UDF you can try.
Sheet2

*	A	B
1	String	Sum
2	Cat Dog Bird	6
3	Dog	2
4	Bird Lizard	7
5	Cat Dog	3

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =sumthings(A2) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

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]

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

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]

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

Easy. This revision is NOT case sensitive:

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]

You can do it with formulas too:

	A	B	C	D	E
1	String	Solution		Word	Value
2	Cat Dog Bird	6		Cat	1
3	Dog	2		Dog	2
4	Bird Lizard	7		Bird	3
5	Cat Dog	3		Lizard	4

<tbody>
</tbody>

Sheet8​

Array Formulas Cell Formula B2 {=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

<tbody>
</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]

You can do it with formulas too:

Your formula is not robust though...

Catholic Dogma Birdie

 

[Rick Rothstein]

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]

I did mention some issues!

There some issues with this.

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