1

In Java, we can use the string.matches(" .* anytext .* ") to see if the string contains the string 'anytext' in it, how does one do the same in Visual Basic for Excel?

  • You might want to see [this](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) – Tehscript Apr 27 '17 at 12:03

2 Answers2

1

You could use the Like operator together with the the wildcard symbol *:

MyString Like "*anytext*"

will return true if and only if MyString contains "anytext".

See this for details.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
0

Use the Instr function

Dim pos As Integer

pos = InStr("find the comma, in the string", ",")

will return 15 in pos

If not found it will return 0

If you need to find the comma with an excel formula you can use the =FIND(",";A1) function.

Notice that if you want to use Instr to find the position of a string case-insensitive use the third parameter of Instr and give it the const vbTextCompare (or just 1 for die-hards).

Dim posOf_A As Integer

posOf_A = InStr(1, "find the comma, in the string", "A", vbTextCompare)

will give you a value of 14.

Note that you have to specify the start position in this case as stated in the specification I linked: The start argument is required if compare is specified.

Answer in : Check if a string contains another string

Community
  • 1
  • 1
Natty
  • 497
  • 1
  • 11
  • 23