Sub прпррп()
l = 1
sAnswer = "<option value=11 22> Hello <option value=33 444> vasya <option value=55 666>"
adad = Len(sAnswer)
For i = 1 To Len(sAnswer)
If Mid(sAnswer, i, 14) = "<option value=" Then
Лист1.Range("J" & l).Value = vita(Right(sAnswer, adad - i), i + 1)
l = l + 1
End If
Next
End Sub

Function vita(asd, ji As Integer)
a = ""
For i = ji To Len(asd)
If Mid(asd, i, 1) = "<" Then
i = Len(asd)
End If
If Mid(asd, i, 1) <> "<" Then
a = a & Mid(asd, i, 1)
End If
Next
vita = a
End Function

Sub прпррп()
l = 1
sAnswer = "<option value=11 22> Hello <option value=33 444> vasya <option value=55 666>"
adad = Len(sAnswer)'Количество символом всего
For i = 1 To Len(sAnswer)
If Mid(sAnswer, i, 14) = "<option value=" Then 'если находит то обрабатывается функция
Лист1.Range("J" & l).Value = vita(Right(sAnswer, adad - i), i + 1)' Передается Обрезанный текст, и символ с которого надо начать копировать
l = l + 1
End If
Next
End Sub

Function vita(asd, ji As Integer)
a = ""
For i = ji To Len(asd)
If Mid(asd, i, 1) = "<" Then'Если найден символ то цикл прекращается
i = Len(asd)
End If
If Mid(asd, i, 1) <> "<" Then'Если не найден символ то текст набирается
a = a & Mid(asd, i, 1)
End If
Next
vita = a
End Function

Sub прпррп()
l = 1
sAnswer = "<option value=11 22> Hello <option value=33 444> vasya <option value=55 666>"
adad = Len(sAnswer)
For i = 1 To Len(sAnswer)
If Mid(sAnswer, i, 14) = "<option value=" Then
Лист1.Range("J" & l).Value = vita(sAnswer, i + 1)
l = l + 1
End If
Next
End Sub

Function vita(asd, ji As Integer)
a = ""
For i = ji To Len(asd)
If Mid(asd, i, 1) = "<" Then
i = Len(asd)
End If
a = a & Mid(asd, i, 1)
Next
vita = a
End Function