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