How to find if a cell contains a substring Excel

Question

I have the following VBA function

Function .....
..........
If (IsNumeric(x) And ((x = "*9999999*") = False)) Then
.......
Else
...........
End If
End Function

I need to know if a string contains a substring "9999999". How can I do this?

Answer 1

You may use the Like Operator.

Sub test()

Dim str1 As String
Dim str2 As String

str1 = "dhfjd9999999dfda"
str2 = "ddsss999dfdfsfd"


MsgBox str1 Like "*9999999*"
MsgBox str2 Like "*9999999*"

End Sub

Answer 2

If Instr(x, "9999999") > 0 Then ... should do it.