Try the following macro. Depending on which sheet holds the data, you may need to change 'Sheet1' to the correct sheet name.
Code:
Sub Test()
Dim StrB As String, x As Long, LastRow As Long, i As Long
With ActiveWorkbook.Sheets("Sheet1")
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For x = 1 To LastRow
If .Cells(x, 1).Value <> "" Then
StrB = .Cells(x, 2).Value
.Cells(x, 3).Value = ((Len(StrB) - Len(Replace(StrB, "1w", ""))) * 1 + (Len(StrB) - Len(Replace(StrB, "2w", ""))) * 2 + _
(Len(StrB) - Len(Replace(StrB, "3w", ""))) * 3 + (Len(StrB) - Len(Replace(StrB, "4w", ""))) * 4 + _
(Len(StrB) - Len(Replace(StrB, "5w", ""))) * 5 + (Len(StrB) - Len(Replace(StrB, "6w", ""))) * 6 + _
(Len(StrB) - Len(Replace(StrB, "7w", ""))) * 7 + (Len(StrB) - Len(Replace(StrB, "8w", ""))) * 8) / 2
End If
Next
End With
End Sub